Can INDIRECT be used to dynamically alter the second range reference in a Named Range

Silver Contributor

I have a workbook that retrieves data from another spreadsheet, the latter being downloaded from my brokerage. Occasionally the number of rows in the downloaded sheet goes up or down, based on buying or selling securities. After retrieving the data into the active workbook, I have a named range that includes all the data i need; and I then use VLOOKUP to access whatever data I need elsewhere in the workbook. As noted the range usually is stable, but when there's a new row or two, or when I've sold some securities so the number of rows declines, I've been manually altering the range reference in the Named Range box.

Today I tried to used COUNT  to capture the number of rows dynamically, and then created an A1 reference and tried to use INDIRECT to reference that from within the Insert Name dialog box. It appeared to accept the INDIRECT, but the named range didn't work. So my question is whether that's even possible.

18 Replies

@mathetes 

Let me play with simple sample. Assume on Sheet2 we have

image.png

and in Sheet1 we'd like to find element form here on position defined in A3

image.png

Direct formula will be 

=INDEX(Sheet2!$E$3:$E$15,$A$3)

With INDIRECT it's like

=INDEX(INDIRECT($A$1& "!" & "$E$3:" & "$E$15"),$A$3)

For the dynamic range, assuming first cell is fixed, we shall only receive an address of the last cell in the range. If we calculate in B1 the size of the range as

=COUNTA(INDIRECT($A$1&"!$E:$E"))

we may substitute E15 as

=INDEX(INDIRECT($A$1& "!" & "$E$3:" & ADDRESS($B$1+2,COLUMN($E$3))),$A$3)

Here it is assumed ranges for all sheets are in column E and we don't insert/delete columns in first sheet with formulas. When above will be bit more complex, but idea is the same.

@Sergei Baklan 

That might work but it's not what my (unclear) question was looking for. Let me see if i can restate it.

 

I have a named range, using Insert....Name.... and in the dialog box the range is specified as shown here:

 
 

And I then use VLOOKUP to reference any number of cells in that named range by formulas such as =VLOOKUP($A23,CurrentActuals,C$5,0)   That works perfectly.

 

The problem is that occasionally I add a row to the range "CurrentActuals" and have always been changing that last reference manually to "$U$60" or "SU$65"   I am wanting to see if I can use INDIRECT to allow the named range to change its parameters.

 

So I created a formula in cell J1 that dynamically changes so it reads "$U$60" or "$U$65"   and then refer to cell J1 it in the Define Name dialog box by means of the Function INDIRECT. The Dialog Box accepts the entry without objection, and shows as displayed here: image.png  

It totally accepts the entry of the INDIRECT function (In fact, in creating this image, I entered it all in lower case and then came back to clip the screen shot and as you can see it not only accepted it, but changed it to upper case. Suggesting to me that it would work.  But it doesn't. And yet cell $J$1 reads exactly as the cell reference in the first image....

 

So my question is simply phrased perhaps in two parts: (1) Does INDIRECT work as part of a cell reference in a named range? (2) If so, is there a syntax that somehow I'm missing? Either in the Cell itself--e.g., should all of it be surrounded by quotation marks?--or in the spot in the Dialog box where the named range is specified?

 

Does this make more sense? It seems to me that it should be very straightforward, especially given that the dialog box accepted the use of INDIRECT......

@Sergei Baklan   Hmm...that reply omitted an image I'd put in it. Here it is. This is the Named Range dialog box without the use of INDIRECT. And it works just fine, as noted.image.png

@mathetes 

 

Could you use Tables to store the data you need.  As you add or remove data your formula would automatically change?

@Wyn Hopkins   I do have the data in the form of a table, using VLOOKUP to access it. The problem is that the VLOOKUP refers to the table by Name, using the Named Range capability. And the difficulty I was having had to do with enabling that named range to vary in size without re-specifying the dimensions.

 

As sometimes happens, I have now found a very functional answer and it works like a charm. I hadn't realized you could write a full formula in the box where you give the range references. Searching via Google led me to this page https://www.contextures.com/xlNames01.html#Dynamic where the resource describes how to use the OFFSET function to specify the dimensions, doing so within the Range Name Dialog box.  I have now a working solution by that means. The full formula doesn't display in this image, but essentially it reads =OFFSET(TopLeftCorner,0,0,NumberOfRows,NumberOfColumns) with those last two in the form of cell references where those numbers can change dynamically, and the "0,0" at the start just says, begin right at that top left corner with 0 offset).

image.png

 So, folks, THAT is how to have the size of a named range be maintained dynamically.  INDIRECT is not at all necessary (and I wasn't using it correctly anyway)....but this is a far more elegant solution.

mathetes

@mathetes 

 

Glad you found a solution

 

When you say your data is in a Table, so you mean a properly formatted Excel Table (with a capital T) like this one...

 

image.png

 

 

I thought that was what I meant, but l am probably using more of a Data Table approach...definitely rows of data with header, but not "Excel Tables"--that's a function or capability that's come along more recently in Excel's history. But it looked to me, as I researched it, as if that still requires a "RESIZE" command when you add rows or columns...No? Bottom line, it could be yet another way to skin the same cat. Excel does have some redundancies like that. Thanks for pointing that one out.
Thanks, Wyn. This has been a most interesting exchange. In my career (now retired) I was director of the HR/Payroll database at my company, and became in the process very adept at topics like data integrity (in Excel, facilitated by your data validation methods). I've used Excel data validation for a decade or more. And used most of Excel's D____ database functions. The TABLE capability per se is new to me...as you noticed, I was relying on Named Range and wanting to dynamically define the range (and now do so using OFFSET)...anyway, I miss the days of printed manuals (which I actually read periodically, to uncover ways to do things more easily than I'd been doing them). On-line references are useful, but you don't "stumble across" the unexpected. Like in this case, I didn't realize Excel had come up with new ways to conceive of and use tables. I've added your site to my reference bookmarks.
You're most welcome,

The pace of change and what's now possible in Excel is amazing. Having started with Excel back in 1998 it hardly change for 10 years but its been amazing to see all the changes in the last 10. The last 5 years has been even more significant with the advent of Power Query and Power Pivot in Excel, truly game changing features that still far too few folks know about.
OFFSET is volatile, I’m certain that @Wyn Hopkins could recommend a non-volatile formula using INDEX.
Well, depending on what you mean by "volatile," that could be exactly what I want, in the sense of "dynamic" or "changing with variations in other data"...that method was one I found online, as noted above.
If you mean "unreliable" then, no, that's not what I want. Time will tell.
Volatile, in Excel terms, means slow. For the explanation, I’ll leave that to @Wyn Hopkins. Perhaps, he’s busy now. Indubitably, @Sergei Baklan could do so.
Volatile means slow?! Really. That's an interesting notion; would not have been standard English, I wouldn't think..
In any event, I can see why OFFSET might be slow in Excel--there's simply, potentially, a lot to resolve, so if it's being used a lot, repeatedly, or in multiple spots in a given workbook, it could indeed slow things down.
In the use that I'm making of it--simply to define the range to which a Name is being applied, it gets used once upon opening a sheet, and never again. Simply used to take care of a data table that can change its dimensions from time to time, and do so without requiring manual redefinition.

@mathetes 

Volatile function doesn't mean that function is slow itself. Volatile function means the function which is recalculating every time when Excel recalculates something else. Obvious example of volatile function is NOW() - it's extremely fast itself, but if we have lot of them in different cells and formulas entire their recalculation on every click could significantly slow overall Excel performance.

 

My view - yes, in general it's better to avoid volatile functions. On the other hand that's not must. With proper design of Excel workbook volatile functions could be effective enough, especially if take into account efforts on maintenance - they don't cost few saved milliseconds in calculations.

 

More about that in two good articles

Excel performance: Tips for optimizing performance obstructions 

Excel performance: Improving calculation performance 

And citation from the latest:

  • Avoid volatile functions such as INDIRECT and OFFSET where you can, unless they are significantly more efficient than the alternatives. (Well-designed use of OFFSET is often fast.)

Well explained, as always! Nonetheless, non-volatile alternatives are preferable, as can be gleaned from the cited article.

I agree that if volatile functions are avoidable then we should avoid them :) @Twifoo 

Often the non volatile formula alternative has more complex syntax but for serious development we should take the time to avoid volatile functions whenever possible.

Obviously, as @Sergei Baklan mentioned above, there are times when NOW() (volatile) is unavoidable.

Thanks to @Twifoo for various non volatile alternative solutions on my blog this year!