Forum Discussion
Can INDIRECT be used to dynamically alter the second range reference in a Named Range
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
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).
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
- OFFSET is volatile, I’m certain that Wyn Hopkins could recommend a non-volatile formula using INDEX.
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.
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......
@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.
