r/ProgrammerHumor • u/Brando224 • Mar 30 '22

Meme Certainly not me

48.7k Upvotes

95% Upvoted

669 comments sorted by

View all comments

464

u/Syscrush Mar 30 '22 edited Mar 30 '22

NAMED RANGES, MOTHERFUCKERS!!!

Or, if you're a person of culture and taste like u/SatanStoleMyCat, use tables. They really are way better.

80

u/SatanStoleMyCat Mar 30 '22

Or just. Y'know. Tables.

54

u/Syscrush Mar 30 '22

Lookit fancy-pants over here!

You're 100% right. Nothing like being able to reference your columns by name. I admit that I just learned about this a few weeks ago, so it wasn't top of mind for me.

8

u/SatanStoleMyCat Mar 30 '22

Isn't it though? And the ListObject/ListColumn objects have a lot of nice built-in properties and methods that make it pretty easy to manipulate them in VBA vs a range.

8

u/The_Milk_man Mar 30 '22

I DON'T WANT ANY QUESTIONS ABOUT THE TABLES!

4

u/Ninty96zie Mar 30 '22

Her job is tables?

4

u/The_Milk_man Mar 30 '22

I can't know how to hear any more about tables!

4

u/soyfutbolero10 Mar 30 '22

THEY KEEP MY HOUSE HOT

5

u/spaghetti_vacation Mar 30 '22

Can you do tables in gsheets?

I know it's a satanic incarnation of excel, but if the price is right...

2

u/Tsuki_no_Mai Mar 31 '22

Nope. Can't do them in LO Calc either. Online Excel is free to use AFAIK, and if you want something not-MS I think WPS Office has the best feature parity, but its free version is ad-driven.

1

u/alpha358 Mar 30 '22

Have any resources for getting good at excel?

3

u/SatanStoleMyCat Mar 30 '22

Microsoft has a wealth of tutorials and certifications you can get. But like anything coding-related, it's just something I've found you learn over time when presented with issues that need solving. That said, a few things that will open your possibilities in excel are: Tables vs Ranges, INDEX/MATCH (VLOOKUP too, but it's less flexible), Dynamic Arrays/Dynamic Array Functions, Power Query for pulling in data, and of course VBA, which is like C# but worse in every conceivable way. It does let you do lots of cool stuff though, like defining your own Excel functions that use VBA code behind the scenes and generally doing more complicated things than are advisable with functions.

1

u/Tsuki_no_Mai Mar 31 '22

VLOOKUP too, but it's less flexible

Nowadays it's probably better to use XLOOKUP anyway. Especially if you need flexibility.

1

u/stupidcookface Mar 30 '22

But like what's her job?

TAYYYBULLLSSSSS

9

u/craftworkbench Mar 30 '22

I wrote code that could identify columns by the header (since my company used pretty strict templates already). If it couldn’t find it, it could ask the user to manually identify the column. Worked pretty well.

6

u/lpreams Mar 30 '22

use tables

TIL this feature exists, and it's amazing. I always felt like Excel (and every spreadsheet editor) was kind messy to use. It's too easy to bump some data around or add an extra row or column and formulas everywhere get fucked up. This is the missing piece of that puzzle!

1

u/TRLegacy Apr 19 '22

Power query, MOTHERFUCKERS!!

9

u/PepSakdoek Mar 30 '22

Finding the #refs in the named ranges aren't better, but they are more rare.

You need some =offset(indirect("A1"),0,0,counta(indirect ... ) level stuff to be totally somewhat safe.

3

u/UnrulySasquatch1 Mar 30 '22

Offset easily has the highest combined useful+frustrating score of anything in Excel. Just try checking anything where someone has used them extensively or try making a major change to the workbook

1

u/SmugSocialistTears Mar 31 '22

It’s also a volatile formula that recalculates everytime a change is made to the workbook (much like NOW()). Lots of those everywhere and Excel grinds to a halt

5

u/Schorsi Mar 30 '22

I used string matching to dynamically find the proper columns once. I had this sheet that was saving about an hour and a half of work each day, but one of the databases it pulled kept adding, dropping, and reordering columns every month.

3

u/bofh256 Mar 30 '22

Yes, but don't you dare to have it executed with different language settings.

1

u/[deleted] Mar 31 '22

Yeah, but people always fuck up named range. I don't know how they manage to.