Excel Help Thread 📊

I have this exact brain problem! I used to use excel all the time for managing budgets and log frames but always have to check this for some reason.

1 Like

I’m not sure I understand what you mean, sorry!

1 Like

Happy to attempt to help if you want to give an example btw

This is fiendishly clever!

No one should beat themselves up because they can’t make sense of something as counter-intuitive as Excel formulas, mate! :slight_smile:

FWIW I think MS created Access as an answer to some of the more detailed stuff here. That said I’ve not used it for 22 years but I remember it being involved in a large system at Ofsted that connected Access, Word and Excel together as you could make Access treat an Excel sheet as if it were a DB table (so this is the sort of thing that might have simplified your issue @still_here where your sheet was starting to be more like a DB).

However, you’ve then got to get into the grass with Access which is probably a dead end in terms of office skills.

Cheers Theo and @froglet. That link does explain textjoin but still includes the first value.

However I think I’ve figured it out by adding another column to take just what’s after the first comma in my textjoin column. Sometimes the simplest solutions are the best it seems, especially with Excel.

I’m not really too fussed about Excel noobness. It’s just one of those tedious bits of life you’re expected to know in certain jobs I think.

1 Like

Would always tend to advise people to break really complex down and just do it in steps using several columns or rows.

3 Likes

Thought he meant destroy your computer and run into the woods naked to live as a hermit but will grudgingly accept your solution also works.

1 Like

This is reserved for when you’re having to use tables in Word.

3 Likes

Massively agree with this. Sticking in a few extra columns for indexes, lookups and intermediate calculations just makes things so much easier to understand and fix. I especially hate it when somebody has exactly the same function repeating multiple times within a single formula instead of breaking it out.

In general I agree but depending on what your spreadsheet is ultimately doing it can cause things to slow down massively. I tend to break things apart and then that normally shows you how you can build it back up into one formulae/column if necessary. Sometimes I build helper columns and index columns (which I then hide) that help with using indirects and things.

Yeah, I mean this workbook I’m on now is massive and it takes some time to calculate. I’m under absolutely no doubt this is because I’m doing things ‘inelegantly’ but, y’know, it works.

Think there’s a space in large businesses for people being so handy at sorting this sort of stuff out.

1 Like

Concersely I hate adding extra columns for stuff you can do in one. Each to their own!

1 Like

Never really thought to use Microsoft Access, had a look and we don’t actually have is which would probably explain why.

Ngl i really like the nerdy excel vba stuff anyway.

1 Like

Is anyone here good at macros in google sheets? How did you learn? Any good resources? Had a look online and couldn’t find any middle ground between incredibly slow-looking, involved and boring, and too high level.

Yeah you probably need a top tier O365 licence for the ability to install it.

It’s complex but you’d probably need a few days to familiarise yourself so obviously that vs just doing it in Excel despite the issues will win out most times.

No, but would be interested in the answer. I’ve tried a couple of times to force myself to learn and not got past the first chapter. However, the company I work at are moving completely away from Office (Excel now needs approval to install) so I’ll have to figure it out eventually.

1 Like

I can’t do macros in excel either

What are you replacing it with?

Access is effectively a legacy database these days. If you really feel the need to get into databases then better just to take the plunge into SQL Server and be done with it.

1 Like

gSheets. Office > gSuite in general. Obviously there are loads of exceptions for legacy applications and things that need 3rd party add-ins for reporting and the like but that’s the strategy.

Ah. The job title in my contract is “senior VBA developer.” That hasn’t been the main thing I do for a while but it could still become a problem if somebody is looking down the list of who to kick out.

1 Like