Multi-Level Data Validation LAMBDAS

multi-level data validation.lambda

/*
Name:  Multi-Level Dropdown Validation (MultiLevelDropdownValidation)
Description:  Returns the list of allowable values for a specific cell based on 
   the entries to the left.
Parameters:
   tablename - name of the Excel table containing the data validation dropdowns.
   header - table header cells from the first column with data validation to the column for which
      to return list of values allowed.
   keylisttable - reference to dynamic range containing the MultiLevelKeyLists lambda key lists table.
Source:  Written by Excel Robot, inspired by Wyn Hopkins (https://www.youtube.com/watch?v=r0DTFodQYJQ&t=989s)
Gist URL:  https://gist.github.com/ExcelRobot/2851d3c70dac9ee4b06e0f233dc059af
*/
MultiLevelDropdownValidation = LAMBDA(tablename,header,keylisttable,LET(
   _level, COLUMNS(header),
   _key, IF(_level=1,"",TEXTJOIN("|",FALSE,TAKE(INDIRECT(tablename&"[@["&TAKE(header,,1)&"]:["&TAKE(header,,-1)&"]]"),,_level-1))),
   _keys, TAKE(keylisttable,,1),
   _list, OFFSET(keylisttable,MATCH(_key,_keys,0)-1,1,COUNTIF(_keys,_key),1),
   _list
));

/*
Name:  Multi Level Key Lists (MultiLevelKeyLists)
Description:  Returns all possible validation dropdown lists need for multi-level data validation.
Parameters:
   table - a table containing all allowable multi-level validation combinations.
Source:  Written by Excel Robot
Gist URL:  https://gist.github.com/ExcelRobot/2851d3c70dac9ee4b06e0f233dc059af
*/
MultiLevelKeyLists = LAMBDA(table,LET(
   fStack, LAMBDA(params,function,LET(
         _firstrow, function(INDEX(params,1,1)),
         _stacker, LAMBDA(stack,param,VSTACK(stack,function(param))),
         REDUCE(_firstrow,DROP(TAKE(params,,1),1),_stacker)
      )),
   fCombos, LAMBDA(level,LET(
         _keys, IF(level=1,EXPAND("",ROWS(table),,""),BYROW(TAKE(table,,level-1),LAMBDA(r,TEXTJOIN("|",FALSE,r)))),
         _values, INDEX(table,,level),
         UNIQUE(HSTACK(_keys,_values))
      )),
   fStack(SEQUENCE(COLUMNS(table)),fCombos)
));

/*
Name:  Multi-Level Check Not In List (MultiLevelCheckNotInList)
Description:  Returns true if the entry is not in the list provided. Used in conditional
   formatting formula for highlighting entries that don't match that cell's data validation list.
Parameters:
   entry - value to check
   list - column vector of allowable entry values
Source:  Written by Excel Robot
Gist URL:  https://gist.github.com/ExcelRobot/2851d3c70dac9ee4b06e0f233dc059af
*/
MultiLevelCheckNotInList = LAMBDA(entry,list,AND(entry<>"",SUM(N(entry=list))=0));

2023-02-06_23-37-46.mp4

Hi @ExcelRobot,

This is pretty cool. 👍 Have been trying to implement something similar but can't seem to figure out how to have these dropdowns in multiple tables referencing the same product set. Toyed with trying to create a function to get the current table's name and pass it into ProductCategoryDataValidation, ProductNameDataValidation, and ProductTypeDataValidation but not having any luck. Any ideas???

CptFujMnky