Google Sheets: Simplify a very complex INDIRECT formula

[Perlapimpim]

Hi guys,

Big challenge here, I'm being asked to simplify a formula we used in the past. It's very effective and powerful but because we would like more flexibility and tweak it sometimes, we need a formula easier to change...
Here is the google spreadsheet with an extract https://docs.google.com/spreadsheets...it?usp=sharing
It's being used for all of our clients that's why it was interesting to be able to copy-paste it and refer to the name of the client (Client #1, #2, #3, ...) instead of adjusting the formula each time for each one of them.

The concerned formulas (the ones I need your help for) are highlighted in yellow.

Thanks so much

[Pepe Le Mokko]

Is this a google sheet problem ?

[AliGW]

Please attach the workbook directly here.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[Perlapimpim]

Yes but the formula is similar to what you would do on excel, no?

[AliGW]

I don't know, but if this is a Google Sheet issue, it needs to be in the correct section. I will move the thread for you.

[Perlapimpim]

Thank you

[rorya]

It looks at first glance as though you could use a few helper cells - such as the repeated date calcs like EOMONTH(TODAY(), -2) and the revenue amounts, and then just refer to those cells directly rather than repeating the whole formula for each one each time.

[Perlapimpim]

Thank you Rorya for your advice Would you mind elaborating, please?

[rorya]

I mean you put any part that you repeat, like the date criteria, into their own cells and then refer to those cells. Eg you put the =EOMONTH(today(),-2) into say F1 and then replace any instance of EOMONTH(today(),-2) in the formula with just F1.

I can't edit your sheet so I can't show you directly.

[Perlapimpim]

Oh sorry you are right!
You can edit this one https://docs.google.com/spreadsheets...it?usp=sharing

[rorya]

OK, so I've added 3 helper cells in E2:E4, and then in column E adjacent to your existing formulas, I've added versions that use these helper cells to shorten the formulas. Hopefully they are correct!

[Perlapimpim]

Hi Rorya
I have not had the time to apply it for my clients, but it seems amazing! Much simpler!! Thank you very much

[processingClouds]

Looking at the formula format it is a google sheet problem. It uses ranges A2:A and so forth.
So i guess it is in the right section.

[AliGW]

Welcome to the forum.

We know it's a Google Sheets issue. The thread was moved to the Google Sheets section by me on 30 July and the title changed accordingly. What is the point you are trying to make, please?