-
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.)
Oct 14 2019 03:05 PM
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.
Oct 14 2019 04:00 PM
Let me play with simple sample. Assume on Sheet2 we have
and in Sheet1 we'd like to find element form here on position defined in A3
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.
Oct 14 2019 04:46 PM
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:
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......
Oct 14 2019 04:50 PM
@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.
Oct 14 2019 05:35 PM
Could you use Tables to store the data you need. As you add or remove data your formula would automatically change?
Oct 14 2019 06:20 PM
@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).
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
Oct 15 2019 01:32 AM
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...
Oct 15 2019 04:42 AM
Oct 15 2019 05:07 AM
You might find this of interest,
https://accessanalytic.com.au/dynamic-data-validation-tables-excel/
Oct 15 2019 05:22 AM
Oct 15 2019 06:16 AM
Oct 15 2019 09:11 AM - edited Oct 15 2019 09:12 AM
Oct 15 2019 10:19 AM
Oct 15 2019 12:27 PM
Oct 15 2019 12:54 PM
Oct 15 2019 01:01 PM
Oct 15 2019 02:06 PM
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.)
Oct 15 2019 02:14 PM
Nov 10 2019 03:06 PM
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!