Forum Discussion
Can't apply data validation with filter function inside. Is it possible?
Sorry, I hadn't thought that one through. The function used to generate the number sequence was
= SEQUENCE(10001, 1, 0, 0.01)
Since 0.01 is not represented as an exact binary number, the error accumulates to finish up at
100.000000000014
For data validation to work, not only do you have to get the 100 to match, the rounding error has to match. Two options appear to work
= SEQUENCE(10001, 1, 0, 1) / 100
= ROUND( SEQUENCE(10001, 1, 0, 0.01), 2 )
Hi Peter
Your solution is amazing but when I try to apply it in my project, It end up with error
I try to apply XLOOKUP()# in Name Manager and Data Validation or directly in table coulmns but non of them work
I think the problem is I can't use XLOOKUP to lookup on single value and return all values
I would be appreciated if you apply your Solution on this sample project
https://1drv.ms/x/s!AqkRTLDuizdFk202mf65NCT_T_6X?e=6TO6Qi
The key features of the proposed solution are that each validation list is created as a dynamic array on the worksheet. Both XLOOKUP and INDEX return the cell they identify as a range reference. Adding the '#' expands the reference to the spilt range. Since it is an actual range on your worksheet, it is eligible to be a validation list despite the unconventional means by which it was identified.