Indirect() syntax problem

**1eyedjack** · 04-23-2010, 04:24 AM

Can someone please help my with the syntax for a formula that returns an undesired #REF! error the way that I have written it?

Problem is:
A worksheet "Summary (0)" contains a table identified by local named ranges.
Another worksheet "Sheet2" in the same workbook contains a cell which names the worksheet wherein that table resides. ie, Sheet2!$E$3 contains the text string "Summary (0)"

Cell Sheet2!$E$5 contains a lookup value.

Cell Sheet2:$E$11 contains a formula which should return a value from the table, but by locating the table from the sheet name contained in cell Sheet2:$E$3.

The problem is illustrated in the attached workbook.

The end result returned by Sheet2:$E$11 should be the same as that which is returned by the cell Sheet2!$E$9 which contains a simplified version of the formula in which the table's worksheet is hard coded into the formula. Currently cell E11 only returns #REF!.

**DonkeyOte** · 04-23-2010, 04:44 AM

I'm afraid that given PAID is itself a Dynamic Range you can't use INDIRECT.

You would need to use Evaluate call (ie old XLM) via a Name to utilise INDIRECT - then use the Evaluate Names in your formulae.

**1eyedjack** · 04-23-2010, 04:57 AM

Originally Posted by DonkeyOte

You would need to use Evaluate call (ie old XLM) via a Name to utilise INDIRECT - then use the Evaluate Names in your formulae.

I'm sorry, but that went completely over my head. I appreciate that you are busy.

**DonkeyOte** · 04-23-2010, 05:14 AM

Because the named ranges Paid and Years are defined by virtue of formulae (rather than by explicit range reference) you can't use INDIRECT formulae to reference them.

Put another way - using your file - the reference(s) to the name(s) must be explicit as opposed to implicit given the names themselves do not use explicit references - as is proven by virtue of Simple versus Complex in your sheet.

The workarounds are then to either

a) use explicit range references in your names (eg ='Summary (0)'!$D$7:$F$7)

b) use the Evaluate work around via additional names

Option a) is obviously not always viable so to demo option b) ... using your file - for simplicity let's create the INDIRECT strings as we need them:

Please Login or Register  to view this content.

With the above in place let's use Evaluate via two new Names, eg:

Please Login or Register  to view this content.

We then utilise the above in our formula:

Please Login or Register  to view this content.

As you alter E3 so the references should adapt accordingly.

To demo. copy Summary (0) and change the paid values - then modify E3 to be Summary (1) and you should find the result alters accordingly.

NOTE: in XL2007 and beyond the above (use of old XLM call) would necessitate file be saved in macro format (eg .xlsm) if using 2007+ extensions.

**1eyedjack** · 04-23-2010, 05:28 AM

Understood now, thanks for the expansion.

The last bit in italics just causes me a bit of worry. Currently the workbook is designed for use in Excel 2003, but where possible I would like it to be capable of running under Excel 2007 in compatibility mode (saving as .xls). Are you saying that this solution would not work under 2007 as it stands? I do not fully understand what you mean by "if using 2007+ extensions". Presumably if NOT using such extensions the comment does not apply, but I am unsure what is meant by "2007+ extensions".

**DonkeyOte** · 04-23-2010, 05:37 AM

From XL2007 you can save as a .xls without issue - it was more the case that if you opted to save in 2007+ format (so as to utilise the much better compression offered) you would have to save as say .xlsm rather than .xlsx

**1eyedjack** · 04-23-2010, 06:21 AM

Great, thanks, that concludes the thread for me.
I generally dislike using Excel 4.0 macros but can live with it as long as the workbook doesn't crash. So far so good.

**1eyedjack** · 04-27-2010, 06:48 AM

Just for the sake of thread completeness, I am sure that you all know this and left it for me to find out as an "exercise for the student", but I could not get the named range which refers to =EVALUATE(expression) to recalculate reliably when the workbook recalculates.

I seem to have got around the problem by amending the name to read

=EVALUATE(expression)+0*NOW()

**1eyedjack** · 04-27-2010, 08:14 AM

Ooops no, that only works if "expression" evaluates to a number, not a text string.
Back to the drawing board.

**DonkeyOte** · 04-27-2010, 08:17 AM

The use of INDIRECT in the Evaluate call should ensure Volatility ... it should thus recalculate on any and every volatile action undertaken in the model if the Application is running in Auto Calc mode.
(remember not all actions are volatile to the extent that they invoke calculation)

Perhaps you could outline a process by which we could replicate the inconsistency you're experiencing ?