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 )
I think the problem goes beyond the specific use of FILTER and arises from the fact that you are offering an array of values to an antiquated bit of functionality that only accepts Range references.
HansVogelaar Is this statement correct? (I have never been successful using an array but that does not mean that it is not possible).
Other thoughts are that some formulas that fail as validation or conditional formatting formulas will still work if the formula evaluation is within a defined name (e.g. range intersection within a CF formula). Something else that may be useful is that an XLOOKUP that matches the header of the validation lists to select one, can then return the spilt range by appending "#" to the formula, e.g.
= XLOOKUP("UnitV", ValidationHeadings, VaildationLists)#
In this instance 'ValidationLists' would be a name given to the range you show in a tan colour. XLOOKUP returns a single cell within the range but, since each validation list is a dynamic array, the "#" returns the entire list.
Data Validation of type List does accept a formula of type =OFFSET(...) as source.
I sorted the list on the In sheet on the Type column, then added data validation to the Ingredient column on the Recipe sheet, with formula
=OFFSET(In!$B$1,MATCH(A2,In!$A$2:$A$9,0),0,COUNTIF(In!$A$2:$A$9,A2),1)
This worked correctly:
However, it doesn't accept structured table references (at least in Excel 2019); the following caused an error:
=OFFSET(In!$B$1,MATCH([@Type],In!$A$2:$A$9,0),0,COUNTIF(In!$A$2:$A$9,[@Type]),1)