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 )
That is the sort of thing that I had in mind when I described Data Validation as antiquated functionality. I think Structured References work provided the formulas are referred to by a Defined Name and not directly. I used to find the calculation performed within the Names context more reliable than data validation, conditional formatting or the grid. These other environments have different failings (XLM4 functions / structured references / array calculation without implicit intersection) and I never really understood why such differences should even exist.
The attached may work, but all the validation lists are pre-calculated as dynamic ranges.
To create a list
= SORT(
UNIQUE(
FILTER(
Ingredient[Ingredient],
Ingredient[Type]=@ValidationHeadings
)
)
)To apply a validation list
"ValidationList"
= LET(
idx, XMATCH(LEFT(@Heading,1), {"I","U","Q"}),
Anchors, CHOOSE(idx, IngredientValidation, UnitsValidation, QuantityValidation),
XLOOKUP(@Type,ValidationHeadings, Anchors)# )
The Quantity validation is generating correct list,
But I can't enter that no. manually. It can be entered only through the dropdown list.
Why is that? Can it be solved?
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 )