Make sure -everyone- has new Excel.
Nothing worse than using the new cool tools only to realize your boss gets an error.
I really like unique() by the way.
2 Likes
I only FINALLY got comfortable using index match without having to google anything. I’m not giving it up yet.
3 Likes
Huh, never used that before. I usually just paste the data somewhere and use the “remove duplicates” button. I’ll have to give it a try!
data-filter-advanced-unique values only-sumthin-sumthin-copy to new location.
Don’t really like how it works, though, upon subsequent unique-values-findings.
1 Like
I like seeing vlookup(a3 , agesex, 2, false)
I hate seeing vlookup(a3, az100:za5000, A$2+16)
Yeah, it’s the same. I just don’t like to bother with that.
Well, usually the same. If you have big balls, you can leave it as a formula, and make your whole spreadsheet hinge on it…
I am the exact opposite. I hate named ranges. They seem to cause no end of problems. I have trouble finding them, and especially changing them. Yes, that is a me-problem. But if I inherit a spreadsheet with named ranges the first thing I do is delete all of them.
I’ve seen numerous errors that were the result of named ranges not actually being assigned as the user believed they were assigned.
So while
vlookup(a3 , agesex, 2, false)
seemed like a perfectly reasonable formula… upon close inspection it turns out that it’s actually excluding ages 0-7 or the female column or something. It may have been set up correctly initially and then some random thing happened to mess it up and no one noticed because the formula looked reasonable.
If the formula was
vlookup(a3, az100:za5000, A$2+16)
It’s much easier to check and see that what you actually want is az100:bc210, and you can easily fix the error.
Hate named ranges. If you need to remember what the range is, add a comment.
Oh, and I realize that I’m probably in the minority and unlikely to convert anyone, but that’s how I see it.
That’s fair too. I have been getting files lately with 250 dead ranges linking to files that are a decade old.
By the way. To find them, highlight (double-click) on the word agesex and press control-g (or f5).
2 Likes
What about named cells?
I’m going through the process of creating an experience study tool. I’m trying to name all my inputs to make it obvious what my formulas are looking up. So instead of looking up Inputs!A5 it’s looking up ExperienceYear, etc. It makes it so much easier for me to understand what the tool is actually doing. I don’t think my brain actually can compute anything after so many nested if’s of cell reference after cell reference.
1 Like
Could you share some idea of what you’re trying to do. Usually a lot of nested ifs is also really bad.
Nested If’s are my lifeblood. Don’t take them away from me, I’m not qualified to be as smart as you all!
Excel really isn’t the best tool for this kind of study, either. But it needs to be done quickly and be customizable, so it is what it is for now.
2 Likes
guilty
Named ranges and named cells preserve my sanity, ESPECIALLY if they’re files where there’s risk of rows/columns being inserted, and I’m using VBA for automation.
When updating quarterly/annually-prepared files, I HATE having to go through and change formulas because there are a few new rows or perhaps extra columns…mostly because I discover that something’s become misaligned or is missing some rows at the most inconvenient time, disrupting my flow.
On top of that, I go through so much scrap paper where I scribble down some formula, and then go look up what the cells referred to really are.
3 Likes
:exams:
So you’ve been my arch nemesis all these years!
Seriously! If you’re smart enough to make a huge mess, then you’re smart enough to not make a huge mess!
Can we PLEASE talk about your formula here? We need an INTERVENTION.
1 Like
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?