Not my style, but less risky than named ranges IMO.
So if I want to look up something based on a classification and I have six tabs of table lookups, what’s is the best way to look that up? Combining into one tab is not feasible
If A, look up Tab A values
If B, look up Tab B values
…
If F look up Tab F values
But I like the one tab idea!
But failing that, can you just pull 6 values? And then pick one.
And blow up my file even more? Psh.
I like the guts of my file to be hidden. I want the file to look polished. On the plus side, if my files work well and add value, but can’t be understood by anyone else, that’s job security for me! …right?
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.)
1 Like
You can intervene on this question for me instead!
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.
1 Like
Okay.
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.
1 Like
I do really like the array functions more than I thought I would.
I like:
SEQUENCE
UNIQUE
SORT
FILTER
XLOOKUP
they’re all super neat
I think INDIRECT is dangerous
4 Likes
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.
you can easily combine to one tab. just add a new column with values TabA, TabB, …
then add a second column that concatenates the 1st with the original key. then lookup this second column.
I used this technique to replace Indirect(), which I don’t like to use anymore
1 Like
seriously this takes like 10 seconds
and once you check it, it’s done. you don’t have to worry about other formulas using the same range
Me: “You are making your formulas too complicated. Let me help you.”
You: “You can help me by answering my other question instead.”
Other question: “How can I make my formulas more complicated?”
2 Likes
Just gonna remind everyone that I did acknowledge both:
and
3 Likes
I’m not a fan of other people’s named ranges.
They are findable using F5. So, faster than Chalky’s estimate of 10 seconds.
1 Like
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.
2 Likes
Agree, but in the cases where I use it, it’s generally the least-bad alternative given a limited time to work in.
(The better answer would be, of course, to avoid situations where it becomes the last-bad option. But time/resource constraints…)
1 Like
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.