Can't apply data validation with filter function inside. Is it possible?

[Nishkarsh31]

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?

@Sergei Baklan @Peter Bartholomew 

[#2]

@Nishkarsh31 

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 

[#3]

@Nishkarsh31 

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.

@Hans Vogelaar  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.

[#4]

@Peter Bartholomew 

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)

[#5]

@Hans Vogelaar 

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.

@Nishkarsh31 

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)# )

[#6]

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?

[#7 · Accepted solution]

@Nishkarsh31 

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 )

[#8]

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

[#9]

@asharif1377 

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.