CONCAT in data validation list

[ITTom365]

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] 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")

=CONCATENATE("val";A1) 

="val"&A1

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

Can anyone help?

[#2]

@ITTom365 

Naming cell A1 to be 'select', 

= IF(select="A", valA, valB)

would return one of the two lists for validation or calculation.

[#3]

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

[#4 · Accepted solution]

@ITTom365 

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)

[#5]

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