I thought we had a thread like this already. 
Maybe I’m thinking of this thread.
Here’s my idea / I’d like to see this option: “[file] is locked for editing by ‘[user]’. 
[Read-only] [Notify] [Tell them to get the hell out] [Cancel]”
8 Likes
[Assert higher rank and kick them out of file]
4 Likes
They’ve got a feedback site (there used to be a different place… and I’m kind of annoyed it’s gone now)
https://feedbackportal.microsoft.com/feedback/forum/c23f3b77-f01b-ec11-b6e7-0022481f8472#
and now I gotta bitch at them again for autoformatting which pisses me off
1 Like
I’d like the ability to kick someone out of a file, but then I think of an ex-boss who’d abuse the shit out of it - especially when he’d go into a file you’d worked on, fuck up something there, then blame others for that fuck-up. Even after I’d inserted code to track changes, which showed he’d been in and been responsible.
VLOOKUP that indexes off a column and looks left / HLOOKUP that indexes off a row and look up. Yes, there’s workarounds to this but they’re painful.
Add to that: the ability to specify the last row/column for a lookup by merely adding LAST as the row/column to return. (Then if you expand the region, you no longer have to go find all your lookups and re-index them to the last row/column.)
Dark mode. 'Nuff said.
Copy/paste of multiple cells when data is filtered.
1 Like
The answer you’re looking for is right there in your post.
It took me a bit to learn INDEX/MATCH but now that I know it’s easy & I wouldn’t have it any other way. V/HLOOKUP is for suckers. ymmv.
4 Likes
They introduced XLOOKUP recently. I haven’t had a chance to look at it, but I think it is supposed to be better than index/match.
That’s what I’ve heard (from mpc) but I doubt I’ll get to that version of Excel before the next decade.
Vlookup is still a little more intuitive/quicker for me, but if I’m doing multiple lookups or the key isn’t on the left then certainly index+match
2 Likes
Always feels really good when you get that index + double match for a bunch of vlookups. Feels like riding a bike with no hands
2 Likes
One thing I always liked that index match could accomplish that hvlookup couldn’t without named ranges was being able to rearrange or insert columns. I assume that’s still the case with xlookup?
Now that I’m thinking about it, probably not, since you’re directly pointing it to the column rather than having a reference relative to a starting point.
Or maybe not. I’ve only used xlookup a few times so I don’t know what I’m talking about.
1 Like
WeightAvg (Data1, Data2) = sumproduct(Data1,Data2) / sum(Data2), where data1 and data2 are the same size area.
4 Likes
I’ve started moving my workbooks from index/match to xlookup, it’s everything I want it to be
1 Like
If they could fix that thing where my formulas break when I do SUMIFS across workbooks I’d appreciate it
2 Likes
xlookup is better than index/match for arrays
1 Like
i use all three: vlookup, xlookup, index/match
depending on the situation
1 Like
-
Dates before 1/1/1900.
-
NOT automatically & irretrievably converting genome-mapping data (or anything else that looks remotely like a date) into dates.
-
At least giving the option to retain leading zeros on numeric strings
3 Likes
I found ways to get around that “problem”. The key is to have a row (column) that enumerates them and point to that in the formula.
Agree with all of those!
Also, I feel like they ought to get rid of 2/29/1900. It’s not a valid date.
They obviously can’t renumber every date from 2/29/1900 forward. That would mess way too many things up.
But they could shift all of the dates from 1/1/1900 - 2/28/1900 forward by 1 and that wouldn’t mess up too much. That’s only 59 days… that happened 122 years ago.
I dunno, maybe that’s just crazy talk. But it’s so dumb that they have an invalid date in there. I’ve not tested to see what day of the week 2/29/1900 is. Do they just have two consecutive days that are the same day of the week? Or are all of the first 59 days on 1900 the wrong day of week already? 
I’m not at a computer to check.
Yes, I know they inserted this bug so that they’d be compatible with Lotus. That hasn’t been a serious business concern of Microsoft’s for a couple of decades now.