Dynamic named ranges and the Indirect() function

Dynamic named ranges and the Indirect() function

Didn't find your answer?

I have a workbook containing two worksheets, named "Sheet2" and "Summary (0)".

"Summary (0)" contains locally defined dynamic named ranges which identify a lookup table.

"Sheet2" contains a cell which identifies the name of the worksheet "Summary (0)".  It simply contains the text string "Summary (0)".

"Sheet2" also contains a lookup value to be identified in the table in "Summary (0)".

I was going to use the INDIRECT() function to obtain the name of the worksheet which contains the lookup table, but I am informed that you cannot use a dynamic named range within an INDIRECT command.

The problem is illustrated here:

http://www.keepandshare.com/doc/1873597/indirectsyntaxprob-zip-april-23-2010-10-16-am-2k

I am informed from elsewhere that there is supposedly a workaround for this, by the following cryptic advice:

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

Does anyone out there know what this means?  Is there another approach that would workaround it? VBA not out of the question.

With kind regards

Clint Westwood

Replies (7)

Please login or register to join the discussion.

avatar
By nogammonsinanundoubledgame
23rd Apr 2010 10:54

Belay that

I understand it now

With kind regards

Clint Westwood

Thanks (0)
avatar
By philrob
26th Apr 2010 13:32

so what was the answer...?

I'm intrigued what was the answer and/or workaround?

Phil

 

 

 

 

Thanks (0)
avatar
By RichardSchollar
26th Apr 2010 15:55

.

Phil

Assume you have a dynamic named range defined as:

=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2$!$A:$A)-1,10)

called 'MyRange' (refers to the data table in B2:Jend where end represents the last row. Next, create another name and define it as:

=EVALUATE(INDIRECT("RC[-1]",0))

and call it 'DynamicLookup'.  Then, if in your sheet you have the text name of your DNR in cell A1 (ie MyRange) you can then use it in a lookup formula/something else with:

=VLOOKUP(SomeValue,DynamicLookup,5,0)

DynamicLookup always refers to the named range in the cell to the immediate left of the formula in which it appears.

Richard

 

Thanks (0)
avatar
By philrob
05th May 2010 16:04

Many Thanks, this will be superbly useful

This technique will be superbly useful for extending ranges of data that I use for dashboards, at the moment I have highlighted where the ranges end with shaded cells and put notes to say 'insert inside the shaded lines'. With dynamic named ranges it becomes unnecessary, as the data set grows so does the named range (assuming that the xxx users don't leave a blank line/column. I have a vague memory of having used these in the distant past - so it is great to be reminded.

There are pretty good examples of formulae to use at http://www.ozgrid.com/Excel/DynamicRanges.htm

To practice, put a mix of numbers and text in column a, starting in A1.

The using the name manager create a new range,

enter a one word range name (e.g. MyRange)

In the 'Refers to box', type the formula = [e.g. =OFFSET($A$1,0,0,COUNTA($A:$A),1)  - this counts up the number of text and numeric cells and extends the range to include all of them.

You can see the range (in excel 2007) highlighted by clicking 'edit' in the name manager and placing the cursor in the 'refers to' box - the 'dashed border shows the range. other offset formulae exist for numbers only etc.

Thanks

Phil

Thanks (0)
avatar
By nogammonsinanundoubledgame
06th May 2010 07:53

I'm moving away ...

... from using named ranges that refer to entire rows or entire columns, due to possible compatibility clashes between Excel 2007/2010 and earlier versions, arising I assume from differences in the number of rows/columns in the worksheet.  In practice I doubt that any problems would arise, but you do get plagued with warning messages when saving filed in Excel 2007/2010 using .xls extensions.

With kind regards

Clint Westwood

Thanks (0)
avatar
By richardpoulter
14th May 2010 14:59

Lookup table sheets

I'm assuming from your 1st post that sheet 'Summary (0)' is one of many different summary sheets that you will need to refer to.

If so, out of curiosity are they all in the same tabular format (i.e. column headings all in the same co-ordinates), or are they all over the place (if so then I guess that explains your dependency of the named ranges referring to the lookup tables)?

Thanks (0)
avatar
By Fastlane
17th May 2010 08:05

Lost & Confused

I understand and use dynamic range names quite a bit, but too have experienced the problem outlined in Clint's original post (i.e. when trying to use them with the Indirect function)

However, I cannot get Richard's solution to work in Excel 2007 - perhaps because I cannot find any EVALUATE function in XL2007. (I get a #NAME? error if I use the DynamicLookup rangename in any function)

I'm also concerned/confused about only using the rangename DynamicLookup in the cell immediately to the right of the cell contaning the name of the data table DNR (i.e. MyRange) as this seems overly restrictive.

I'd apprecaite if some further explanation could be provided about EVALUATE and the chain of links between the lookup formula (vlookup or hlookup), DynamicLookup, and MyRange (and the values returned by each)

 

Thanks in advance

Colin

Thanks (0)