Using Indirect with Dynamic Header Name

Please upload your workbook sample with expected result.

Try

Then press Control+Shift+F3 in the selected table you will get a window called create name from selection ->>> check mark only top row then ok

now press in A1 cell Alt->A->V->V in setting tab at allow list select list. The in source put =INDIRECT($A$3) and then ok.

This is the way to create a dependent drop down list.

Check the attached file.

Hope this will help you.

Originally Posted by stan101

Thanks, but what is the purpose of selecting cell A1?

oh sorry mistakenly I typed that A1 instead of G2

Originally Posted by stan101

....I wany to create a bunch of column headers and want to use cell A3's contents as the name of the column header to be referenced. I can't seem to get the drop down list to work when trying this...
Thanks

Hello Stan,

INDIRECT is a powerful formula, but this will slow down your worksheet performance. As you said if you have many headers you must be defined name for each header in order to work INDIRECT.

Here is alternate method.

Press Ctrl+F3 >> New.

Name: Headers
Refers to:

=Ancillary[#Headers]

Again press Ctrl+F3

Name: Data
Refers to:

=Ancillary[#Data]

In A3 Data Validation >> List

Source:

=Headers

In G2, Data Validation >> List

Source

=INDEX(Data,,MATCH(A3,Headers,0))

So this way you can have as many column headers without defining individual names.