Using a named cell for dynamic list

[Paul_LARREGLE]

Hi there,

I have several experinces with Excel but this one bug me.

With several option such as "yes/no" i create in a cell (lets say its A1) a name with "concatenate" formula.

For exemple if I choose "boys" then "40_years_old" then "blue" A1 will be :"boys_40_years_old_blue".

So this cell value ("boys_40_years_old_blue") is also a name that refers to several other cells, lets say A1:G1 on sheet 2.

I want to make a list based on this name. As you understand A1 value will change with the choice you do above. So my list change through the name, that change through our choice.

Lets say A2 is my final list. It should propose me the cells that are named "boys_40_years_old_blue", so I should have the choice between "sheet2A1:G1" value.

Instead the only choice I have is "boys_40_years_old_blue". I tried put the concatenate formula in the list windows, I tried to refer to A1 as text. Nothing works.

Can anyone help me in there ? I know the other tricks for dynamic list, but this one would work great for me.

(I dont want to use VBA for several reasons)

Thanks a lot,

Paul

[#2]

@Paul_LARREGLE 

Does =INDIRECT(A1) work?

[#3]

@Hans VogelaarThank you for your answer,

No it doesnt

[#4]

@Paul_LARREGLE 

In that case, I don't understand what you want. Could you attach a sample workbook and indicate in detail what you want to accomplish?

[#5]

it acts funny if the list has concatenated cells, i tried enclosing it in the text function, it still acts funny and is unrelaiable.  have you tried doing a power query on your concatenated list sheet and base your list on the result by associating it first with a named range?