Forum Discussion
Can INDIRECT be used to dynamically alter the second range reference in a Named Range
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.
- 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.
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...
- 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.
You might find this of interest,
https://accessanalytic.com.au/dynamic-data-validation-tables-excel/
