Forum Discussion
CONCAT in data validation list
That would do it. There are a number of functions that return a range reference given a name or an index. These include IF, CHOOSE, XLOOKUP, INDEX/XMATCH, OFFSET. Using a default Table
= CHOOSE(XMATCH(listName,Table1[#Headers]),Table1[Column1],Table1[Column2]) = XLOOKUP(listName, Table1[#Headers], Table1) = INDEX(Table1, , XMATCH(listName,Table1[#Headers])) = OFFSET(Table1, 0, XMATCH(listName,Table1[#Headers]) - 1, ,1)
Naming cell A1 to be 'select',
= IF(select="A", valA, valB)would return one of the two lists for validation or calculation.
Hi, I will try this to see it in action but it doesnt fit my wider need (various validation lists based on users choice), I dont want to have multiple if statements to find the right list.
I renamed my named ranges (lists) to match the options in a master validation list, so that a user chooses an option in the master validated list and that option will correspond with a named range (other lists).
for the dependant lists
=indirect(Cell_Ref_Master_List_namedRange_Choice) - works (but is volatile)
=Cell_Ref_Master_List_namedRange_Choice - only show the text "Cell_Ref_Master_List_namedRange_Choice" and not the items in the list range
=namedRange - works but is not dynamic
List validation source seems complicated
That would do it. There are a number of functions that return a range reference given a name or an index. These include IF, CHOOSE, XLOOKUP, INDEX/XMATCH, OFFSET. Using a default Table
= CHOOSE(XMATCH(listName,Table1[#Headers]),Table1[Column1],Table1[Column2]) = XLOOKUP(listName, Table1[#Headers], Table1) = INDEX(Table1, , XMATCH(listName,Table1[#Headers])) = OFFSET(Table1, 0, XMATCH(listName,Table1[#Headers]) - 1, ,1)- Thank you for your input, unfortunately Im stuck with Office 2016 for the foreseable - so some of the newer functions are unavailable. In the end I decided to do the dynamic validation lists in VBA I will relook if our IT department ever decide to upgrade our office installations
