CONCAT in data validation list

Occasional Contributor



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] and valB=TableB[ColA]


In TableC a validation list correctly populates if the source is set to valA or valB. 


I am trying to build the source based on text in an adjacent column rather than hard coding it. 


I have tried various ways to build the source string based around the ideas below

  (where A1 = "A" or "B")




all attempts fail with the error "the list source must be delimited or make reference to one line or column"


Can anyone help?

4 Replies


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




best response confirmed by ITTom365 (Occasional Contributor)


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