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.
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 projecthttps://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.
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)
