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...
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/
- 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.