Forum Discussion
Can INDIRECT be used to dynamically alter the second range reference in a Named Range
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
- 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, SergeiBaklan 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.