Using CONCATENATE in the validation source field t

Question

My question originates from a task I have been given that has had me stumped. I have been given a large tree structure that I need to turn into a series of dependant dropdown (or something similar). The end goal is to form a means of user input in an excel spread sheet.

I am using Excel 2010 and as such would prefer to avoid VBA.

So far I've managed to cut down the number of lists by using a drop down for the first two generations in the tree and then two successive vlookups. The next generation however needs to move back to a drop down menu.

Is it possible to use the SUBSTITUTE and CONCATENATE functions as a mean of referencing a unique table name within excel. The purpose of this would be to populate a dropdown validation list.

e.g. If cells A1:C1 are defined as:

A1 = Food
B1 = Fruit
C1 = Apples
D1 = <Empty, result here>

And I apply the aforementioned functions:

D1 = SUBSTITUTE((CONCATENATE(A1,B1,C1))," ","")

I will get the output:

D1 = FoodFruitApples

Now say I have a list with the name "FoodFruitApples":

Granny Smith
Golden Delicious
Pink Lady

How could I create a cell containing a drop down list with the table "FoodFruitApples"? Ideally this would all be contained within cell D1 without any hidden columns.

This is my first time posing an excel related question so I apologise if my terminology and question structure is off. Thank you for your help.

Answer 1

You're very close. Say that the range / table for your list is named FoodFruitApples. In the data validation window, for list value you can enter:

=INDIRECT(SUBSTITUTE(CONCATENATE(A1,B1,C1)," ",""))

That should refer to the named range / table for the dropdown to be filled with.