Forum Discussion
Solved
Can't apply data validation with filter function inside. Is it possible?
I wanna put a conditional data validation inside a Table.
It should come as a list
I'm attaching the reference file along. Is it possible?
SergeiBaklan PeterBartholomew1
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)# )- It works. There's just one major issue.
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 )
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)
If you sort the table on the In sheet by Type, you can use the usual OFFSET/MATCH/COUNTIF formula for dependent data validation - see Dependent Drop Downs from Sorted List
