How to change contents of dropdown list based on a criteria

[Gaurav Vohra]

In the attached file entries under the header DATA is the available data. This may extended upto 15-20 columns & 5000 rows.
Output needed is in r.h.s table.
Based on the selection in the first column of r.h.s table, entries available in further columns will change as per availability in the DATA.

 

[Peter Bartholomew]

It might be better to have named ranges for each validation list and then use INDIRECT to access them. As it is, I suspect this Excel 365 solution is unnecessarily complicated.

= LET(
  start, XLOOKUP(@p, product, spec1),
  bottom, INDEX(spec1,24),
  trimmed, start:bottom,
  count, XMATCH("XXX", IF(ISBLANK(trimmed),"XXX",trimmed)),
  validationList, OFFSET(start,0,0,count-1),
  validationList )

 

[Peter Bartholomew]

A more concise formula to determine the number of terms before the first blank record would be

= XMATCH(TRUE, ISBLANK(Trimmed)) - 1

My attention was more on the definition of trimmed.