Indirect on named cell

[Skovgaard]

Hi All,

Can anyone explain why Indirect("fMMBUAR") doesn't work on below reference?

/Skovgaard

 

[Habtest]

Unless the value in Quote!$O... is a string of cell reference (e.g. A1), INDIRECT will lack a valid cell reference.

Is it the cell value in Quote!$O you are looking for? Then "=fMMBUAR" should work.

 

[Alex Blakenburg]

To take that one step further, what row are you entering the "=fMMBUAR" on and what is in the content of Quote!$O with the same row number.
If its not a valid Cell reference or range name, it will return #REF!,
if its pointing to an empty cell is will return 0.

 

[RoryA]

You can't use INDIRECT with a dynamic named range like that. It will only work with a fixed range/address.

 

[Alex Blakenburg]

You can't use INDIRECT with a dynamic named range like that. It will only work with a fixed range/address.

I tested it and it does work.

 

[Skovgaard]

Unless the value in Quote!$O... is a string of cell reference (e.g. A1), INDIRECT will lack a valid cell reference.

Is it the cell value in Quote!$O you are looking for? Then "=fMMBUAR" should work.

I have a table with several things to lookup. In this table, could one cell have the value "fMMBUAR".
fMMBUAR is created as a named range, in below example A5:A7 (Dynamic).
In C5:C7 (dynamic) I would like to retrieve the value of the correspondent row in column A.

It is important, that the lookup "name" is picked from the lookup table.

Maybe Indirect isn't the proper way to do so, in this case I'm looking for other suggestions.

/Skovgaard

 

[RoryA]

I tested it and it does work.

What did you test? =INDIRECT("fMMBUAR")?

 

[Alex Blakenburg]

What did you test? =INDIRECT("fMMBUAR")?

I tried to replicate Skovgaard's scenario.

20210414 Indirect of a range name containing Index.xlsx
	A	B												N	O	P
1	retrieving this value indirectly ---->	10													Column used in index function
2	Test using a range name "RetrieveThis" in the Instead of a cell reference ---->	20
3
4
5	Basic formula	10													B1
6	Moved Index part to a range name	20													RetrieveThis
7	Moved Index part to a range name and ref Quote sheet	20
8
Sheet1

Cell Formulas
Range	Formula
B5	B5	=INDIRECT(INDEX($O:$O,ROW(),0))
B6	B6	=INDIRECT(fMMBUAR)
B7	B7	=INDIRECT(fMMBUAR_Quote)

Named Ranges
Name	Refers To	Cells
fMMBUAR	=INDEX(Sheet1!$O:$O,ROW(),0)	B5:B6

20210414 Indirect of a range name containing Index.xlsx
	A													N	O	P
1															Column used in index function
2
3
4
5
6
7															RetrieveThis
8
Quote

 

[RoryA]

=INDIRECT(fMMBUAR) is not the same as =INDIRECT("fMMBUAR")

The original situation appears to be a cell containing the name of the range, hence the use of INDIRECT. That is equivalent to =INDIRECT("fMMBUAR"), not =INDIRECT(fMMBUAR). The latter will evaluate whatever fMMBUAR refers to and pass that to INDIRECT.

 

[Habtest]

I have a table with several things to lookup. In this table, could one cell have the value "fMMBUAR".
fMMBUAR is created as a named range, in below example A5:A7 (Dynamic).
In C5:C7 (dynamic) I would like to retrieve the value of the correspondent row in column A.

It is important, that the lookup "name" is picked from the lookup table.

Maybe Indirect isn't the proper way to do so, in this case I'm looking for other suggestions.


View attachment 36660

/Skovgaard

I assume there are other customized ranges and you'd like to invoke them by typing in B1?

I cannot think of a simple solution, other than listing all the ranges in a IFS() or CHOOSE(). (Assuming you have Office 365, btw better to complete that in your profile)

=CHOOSE(MATCH(B1,{"fMMBUAR","AA","BB"},0),fMMBUAR,2,3)

Would be interested to know if there's a function for this task.