Indirect on named cell

[Skovgaard]

Hi All,

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

/Skovgaard

 

[RoryA]

If the named ranges are, or can be, made into tables, then you can use INDIRECT (even though those are technically dynamic) and pass the table/column names.

 

[mikerickson]

I notice that the definition of fMMBUAR could be changed from =INDEX(QUOTE!$O:$O, ROW(), 0) by select a cell in row 1 (of what ever sheet you're on) and defining fMMBUAR as =QUOTE!$O1

 

[Skovgaard]

If the named ranges are, or can be, made into tables, then you can use INDIRECT (even though those are technically dynamic) and pass the table/column names.

My intention is to put it into a table.
When I try this, it works if the formula is outside the table. If I want to put the formula in the same table, I get a #SPILL! error. Is that not possible to do?

 

[RoryA]

What are the definitions you are using?

 

[Skovgaard]

I notice that the definition of fMMBUAR could be changed from =INDEX(QUOTE!$O:$O, ROW(), 0) by select a cell in row 1 (of what ever sheet you're on) and defining fMMBUAR as =QUOTE!$O1

Yeah you're right about this. I still need to pick up the name (fMMBUAR) from another table, and INDIRECT doesn't seem to be working with this dynamic cell.

 

[Skovgaard]

What are the definitions you are using?

 

[RoryA]

I suspect that if you use that in another table, then unless all your ranges are the same size as that table, you'll have problems unless you wrap your INDIRECT in an INDEX formula to extract specific cells.

 

[Skovgaard]

I suspect that if you use that in another table, then unless all your ranges are the same size as that table, you'll have problems unless you wrap your INDIRECT in an INDEX formula to extract specific cells.

I'm not sure how you would wrap the INDIRECT into INDEX, could you specify?

Below is the example I'm trying.
The formula in B2 is the same as in D2. As you can see, the formula works outside the table, but not inside.
Is it because it can't make an array formula in a table?

/Skovgaard

 

[RoryA]

Is it because it can't make an array formula in a table?

I suspect so. What I was suggesting was something like:

=INDEX(INDIRECT("fMMBUAR"),ROW()-1)

for example.

 

[Alex Blakenburg]

=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.

I see what you mean. I have now tested it by putting it in a cell and as you pointed out it returns #REF!