INDIRECT doesnt work properly

[Xalova]

I dont know if i just dont understand things, but as for my understandings this formular just doesnt work for me in this scenario.

I want to refer to an already existing name with INDIRECT(). I want this range to end up as my dropdownlist.

The name i want to refer to is called "DropDown1".

The formular in this name is as follows:
=INDIRECT("'Aufstellung Verbrauchsmaterial'!Z"&ROW(Headers1)&"S"&COLUMN(Headers1)&":"&"Z"&ROW(Headers1)&"S"&COUNTA(FILTER(Taaa1[#Headers];IFERROR(NOT(SEARCH("spalte";Taaa1[#Headers]));TRUE())))+COLUMN(Taaa1[[#Headers];[Name]])-1;FALSE)

what this does is that i get a range in the RC format - in my foreign language (Z=R and S=C). (In this example R1C4:R7:C4)
The "Headers1" are also names for a range. Also Taaa1 is an existing table.
I dont know if at this point its better to post my file, but it is written in german..

now when i want to refer to that name ("DropDown1") i just get the #REF! error.

what do i do? now?

 

[jasonb75]

I was a little confused by @RoryA's comment as well, I've used INDIRECT with ranges that I would consider to be dynamic, with no issues. Perhaps it is an indirect reference to a range that has already been created by indirect that is the issue? Again, something that I'm sure I've done previously but could be wrong, my long term memory does only go back as far as breakfast.

Looking at the actual purpose of the formula, I'm wondering if a workaround could be achieved with the areas argument of index, for example

=INDEX((Dropdown1,Dropdown2,Dropdown3),,,--RIGHT(D17,1))

Although this does make several assumptions and would be less practical if there are a lot of options in D17.
This does work in simple form, although I haven't tested it with anything as detailed as your full requirement.

 

[RoryA]

No, it's not a fixed reference - it expands/contracts due to the COUNTA. INDIRECT doesn't work with any references involving formulas - see for example this old thread on DDOE.

 

[Xalova]

I was a little confused by @RoryA's comment as well, I've used INDIRECT with ranges that I would consider to be dynamic, with no issues. Perhaps it is an indirect reference to a range that has already been created by indirect that is the issue? Again, something that I'm sure I've done previously but could be wrong, my long term memory does only go back as far as breakfast.

Looking at the actual purpose of the formula, I'm wondering if a workaround could be achieved with the areas argument of index, for example

=INDEX((Dropdown1,Dropdown2,Dropdown3),,,--RIGHT(D17,1))

Although this does make several assumptions and would be less practical if there are a lot of options in D17.
This does work in simple form, although I haven't tested it with anything as detailed as your full requirement.

There are 12 options right now, and it will be growing in the future. So your solution would indeed be not so practical. Maybe RoryA is right. It would be sad, but it seems logical to me what he states. Althought he aint completly right, as i was able to do such a variable dropdownlist before.

 

[jasonb75]

I'm not saying that Rory is wrong, but I'm convinced that I've used something similar with INDIRECT previously.
Given that there can be some extremely big differences between similar and identical in terms of excel formulas, it is likely that what I have in mind will not work for what you need. I'll have a look later and see if I can find it.

 

[Xalova]

I'm not saying that Rory is wrong, but I'm convinced that I've used something similar with INDIRECT previously.
Given that there can be some extremely big differences between similar and identical in terms of excel formulas, it is likely that what I have in mind will not work for what you need. I'll have a look later and see if I can find it.

Thank you very much.

if it helps, and if you know a way to translate a whole excel spreadsheet, i could try to give my file to you

 

[RoryA]

Althought he aint completly right, as i was able to do such a variable dropdownlist before.

You can certainly use INDIRECT in this way if the named ranges you are using are of fixed size. Other than with tables, INDIRECT will not work with dynamic references.

 

[jasonb75]

I found the workaround that I used.

In short, it doesn't work with dynamic ranges if the dynamic part is defined in a name, but it does work if it is a text string in a cell (this may be the same as the link that @RoryA provided in post 12, I didn't get as far as reading that one).

I should point out though that the formulas I used were much simpler than yours and didn't include table names, this might work but no promises.

First of all, add a new sheet to your workbook (can be hidden later if needed).

Enter the formula used for 'Dropdown1' into a cell on this new sheet, without the INDIRECT part, e.g.

="'Aufstellung Verbrauchsmaterial'!Z"&ROW(Headers1)&"S"&COLUMN(Headers1)&":"&"Z"&ROW(Headers1)&"S"&COUNTA(FILTER(Taaa1[#Headers];IFERROR(NOT(SEARCH("spalte";Taaa1[#Headers]));TRUE())))+COLUMN(Taaa1[[#Headers];[Name]])-1

This will now show the range (with the sheet names where appropriate) in the cell as a text string. The next part is to change the definition of the named range, 'Dropdown' so that it refers to this cell.

Now, where you have INDIRECT(D17) returning a #REF! error, you should hopefully be able to use

=INDIRECT(INDIRECT(D17),FALSE)

without errors.

 

[Xalova]

I found the workaround that I used.

In short, it doesn't work with dynamic ranges if the dynamic part is defined in a name, but it does work if it is a text string in a cell (this may be the same as the link that @RoryA provided in post 12, I didn't get as far as reading that one).

I should point out though that the formulas I used were much simpler than yours and didn't include table names, this might work but no promises.

First of all, add a new sheet to your workbook (can be hidden later if needed).

Enter the formula used for 'Dropdown1' into a cell on this new sheet, without the INDIRECT part, e.g.

="'Aufstellung Verbrauchsmaterial'!Z"&ROW(Headers1)&"S"&COLUMN(Headers1)&":"&"Z"&ROW(Headers1)&"S"&COUNTA(FILTER(Taaa1[#Headers];IFERROR(NOT(SEARCH("spalte";Taaa1[#Headers]));TRUE())))+COLUMN(Taaa1[[#Headers];[Name]])-1

This will now show the range (with the sheet names where appropriate) in the cell as a text string. The next part is to change the definition of the named range, 'Dropdown' so that it refers to this cell.

Now, where you have INDIRECT(D17) returning a #REF! error, you should hopefully be able to use

=INDIRECT(INDIRECT(D17),FALSE)

without errors.

Thank you for your help. Though i have found another solution.
For that i Created another table, filled with all the table names. In the next column i have simply count how many Tables i have entered there. I needed that to have the corresponding number for "Headers", since i do not only have "Headers1" i do also have "Headers2", "Headers3".... etc.

My formula is as follows:

="'Aufstellung Verbrauchsmaterial'!Z"&ROW(INDIRECT("Headers"&VLOOKUP(AZ2,Tab_Tabellennamen[#All],2,FALSE)))&"S"&COLUMN(INDIRECT("Headers"&VLOOKUP(AZ2,Tab_Tabellennamen[#All],2,FALSE)))&":"&"Z"&ROW(INDIRECT("Headers"&VLOOKUP(AZ2,Tab_Tabellennamen[#All],2,FALSE)))&"S"&COUNTA(FILTER(INDIRECT(AZ2&"[#Kopfzeilen]"),IFERROR(NOT(SEARCH("spalte",INDIRECT(AZ2&"[#Kopfzeilen]"))),TRUE())))+COLUMN(INDIRECT(AZ2&"[[#Kopfzeilen];[Name]]"))-1

and it works like a charm. I wouldnt have been able to ind that out without your help and rorys. Thank you, both of you