SOLVED

CONCAT in data validation list

Occasional Contributor

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?

4 Replies

@ITTom365 

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

 

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