Formula results in a defined name

[martynduerden]

Hello,

I have a complex Excel workbook with a lot of data to process in to a single result,

My current issue is I have a VLOOKUP formula which results in a defined name- great! but when I reference that result in another formula I get a #REF error, if I type the defined name directly in to the formula (index btw) all works but I need the formula to recognise the referenced cell.

Example

=VLOOKUP(B8,Cable.Table,2) this results in a defined name of a table (D1A) in cell B30

The cell B30 is named "table"
So

I then have another formula in B33 which is:

=index(B30,B47,C12) or =index(table,B47,C12) Which gives me the #REF error

If I input =index(D1A,B47,C12) the function works but it needs to be the initial reference from the result of the other formula and recognise that it is a defined name.


Any help much appreciated.


Martyn

[DonkeyOte]

You need to use INDIRECT

=INDEX(INDIRECT(table),B47,C12)

Note: INDIRECT is Volatile - see the link in my sig for more info. on Volatile Functions.

[martynduerden]

Thank you for that, I have read the link in the Sig but I am not sure what the risk is with "volatile" functions - can the result actually change if non of the precedents have changed?

Cheers

Martyn

[DonkeyOte]

Volatiles if over-used will affect performance given they are recalculated regardless of dependencies... XLs' calculation engine is pretty smart and only calcs cells that require calculation... Volatiles are the exception to this rule.

So even if the value in your "table" cell does not alter any cell containing INDIRECT will recalculate whenever XL recalcs regardless of whether the contents of "table" have altered (in the case of INDIRECT it's too complex / time consuming for XL to evaluate the INDIRECT and determine as to whether or not cells using the function need to recalculate or not, safer / quicker to say "always recalculate")

Generally speaking Volatile functions should be avoided whenever possible, however, in this instance there is seemingly no viable non-volatile formula alternative.
(based on my understanding of your setup)