Forum Discussion
Solved
CONCAT in data validation list
Hello, I am trying to dynamically select a table column as a validation list (without using indirect) based on text in an adjacent column. I have created two named ranges valA =TableA[ColA]...
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)
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