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?

 

[mart37]

I think you have switched row and column in the second part.

 

[Xalova]

I think you have switched row and column in the second part.

Sorry, i didnt stated it earlier. But if i just go "=DropDown1" i get the expected values. So i dont see why it shouldnt work with "=INDIRECT(DropDown1)". Thats like the main problem

 

[mart37]

When you use "=INDIRECT(DropDown1)" you mostly get the first cell of the range and not the whole range.

 

[Xalova]

When you use "=INDIRECT(DropDown1)" you mostly get the first cell of the range and not the whole range.

I do not even get that. I only get #REF! error.

 

[mart37]

Sorry I am get now the same error.
I think there is some difference in the notation: D1:D7 or R1C4:R7:C4
With the first I get less errors.

 

[RoryA]

INDIRECT only works with fixed references. It will not work with a dynamic range, unless it's a reference to a specific Table (or Table column).

 

[jasonb75]

Why do you want to use =INDIRECT("Dropdown1") instead of =Dropdown anyway? I don't see from your post how it would serve any useful purpose.

Perhaps if we knew more about the required end result rather than the attempted method then we might be able to suggest a suitable alternative.

 

[Xalova]

Why do you want to use =INDIRECT("Dropdown1") instead of =Dropdown anyway? I don't see from your post how it would serve any useful purpose.

Perhaps if we knew more about the required end result rather than the attempted method then we might be able to suggest a suitable alternative.

The main goal out of this is that i have a variable dropdown list. The formula isnt

=INDIRECT(DropDown1)

per se, its

INDIRECT(D17)

and in cell D17 is the value "DropDown1"

I want to have a dropdownlist based on the value of the cell D17, thats what i wanted to achieve with that

 

[Xalova]

INDIRECT only works with fixed references. It will not work with a dynamic range, unless it's a reference to a specific Table (or Table column).

But my Value in the name "DropDown1" is fixed, isnt it? it only changes when the table expands or i enter new values in a table. But The value of "DropDown1" is always fixed as, for example, R1C4:R1:C7