Forum Discussion
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] 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?
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)
