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.
.
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
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
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)?
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