Last active
July 22, 2024 21:04
-
-
Save ExcelRobot/2851d3c70dac9ee4b06e0f233dc059af to your computer and use it in GitHub Desktop.
Multi-Level Data Validation LAMBDAS
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* | |
| 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)); |
Author
ExcelRobot
commented
Feb 7, 2023
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
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment