One of the files I periodically maintain is a registry of large losses. For various reasons (mostly my not having the time or an intern to build something better), I’m pulling data from a corporate source that manifests as an Excel pivot table. Because I am pulling individual claims across umpteen years of history and need the development of those individual claims…well, the underlying source has performance issues such that I have one tab per calendar year.
In preparing an aggregate table containing relevant details from the umpteen tabs, I abuse INDIRECT functions. I have one column where I set up the text for the range I want to query, and then I reference that column with the INDIRECT function in my actual data columns.
(I really need to get an intern assigned to me, or some free time to improve my R skills.)
Are you worried about speed or filesize or what?
I’d think my solution is about the same filesize.
If speed was an issue, you could add if-statements to the 6 cells.
You are definitely the monster that has been haunting my dreams for the last 20 years.
I find it easier to check named ranges than an explicit range; especially if the range is on another worksheet.
Near the top left corner of your screen (left of the formula bar), is a display that shows the coordinates of the active cell (or the coordinates of the top left corner of a range). You can start typing in the named range in that display and a listing of all references that start with what you’ve typed shows up.
Simply select the named range you’re interested in. And here is the part I like best about named range when checking: you’ll go to that named range location with the entire range “selected”. So you can immediately start checking what that range entails.
Checking the named range in depth once allows me then to review other formulas faster that reference that named range.
Adjusting the reference for a named range isn’t difficult either; but is a bit more involved than just giving a description of where to do it. But agree that this is one area that those who modify a workbook fail to do.
HOWEVER, making a change that will affect the reference of a bunch formulas will see quickly that using named ranges makes such an update a much simpler process: namely, you only need to make the change in one shot and not have to hunt the workbook for that reference.
I agree. I’ve found one legitimate use: importing/replacing a bunch of worksheets of data. INDIRECT allows you to delete the old worksheets and move/copy the new worksheets into the workbook w/o breaking all of the formulas.
Note that in this situation, I create a “staging” area to extract the needed info from the data-containing worksheets using the INDIRECT, then the rest of the workbook references this staging area for the needed data.
lol, to be fair Twig, and I say this with all the lightheartedness there is, you regularly over-dissect other people’s posts, so it’s funny to see you bristling at the tables being turned.
I agree, it is the last/worst-case solution. I use it for display when I info I have to pull from multiple tabs but in the exact same cell (times a bunch of cells).
NOT for doing any more, or critical, calculating.
NOT for more than, say, 1000 formulae.
Example: I create, using a macro, 150 tabs of the exact same report onto one file. I want to check the contents of these tabs (for calc errors) without having to look at each one, so, I INDIRECT all the cells I have to look at into one new tab.
Easy-peasy review.
Wait. What’s wrong with MAXIFS and MINIFS? Or are you stuck with an older version of Excel?
(Also, the IF functions are an abomination. Making the thing you’re going to sum, average, whatever the last argument is an affront to all that is good and reasonable. Variable arguments should come at the end of the argument list! IFS functions forever!)
I use indirect all the time. Specifically Indirect(Address(Match… Allows one cell to be variables/variables that you want to see and the match finds where they are in the report. Address allows a different cell to reference the tab that is wanted when they are many different tabs with different reporting dates, etc.
I agree it is dangerous if, in my instance, the numbers inside of address are hard coded. Numbers will always be pulled in but hard to check to make sure they are the correct ones