Is there a way to create 'dynamic' formulas?

Alan Rappa
Alan Rappa ✭✭✭✭
edited 08/13/20 in Smartsheet Basics

As I'm sure all of you deal with every day, my stakeholders are constantly changing their minds about what they want to appear on our dashboards.

No biggie, I just go and modify the formulas, and all is good... until the next change... and the next change.. and the next change...

So, I started to wonder if I could use a combination of IF statements and checkboxes to create more dynamic formulas to avoid having to modify them day after day.

Here is an example formula I use on my Dashboard Metrics Page.

=SUMIFS({Investment}, {Area}, [Metric 1]@row, {Target Completion Q}, "2020-Q1", {Cancelled, Hold, Backlogged}, <>1, {Team_check}, <>1, {Risk-Loss}, <>1)

This sums the investment column, when the area = X, and the Target Completion Q is 2020-Q1, the project is not cancelled, on hold, or backlogged, and team_check is NOT checked and RIsk-Loss is not checked.

This evening, after a day of meetings, one of my stakeholders said they want to show all Risk-Loss items, meaning regardless if the box is checked or unchecked.

That means I need to go into my metrics page, and update all the formulas to disregard that IF.

There has to be a better way, right? I was thinking, that if I could just have some checkboxes on my metrics page that if checked included the appropriate criteria in the formula.

For example, I could have a column called "Include Risk Loss", and when the box is checked in that column, the "{Risk-Loss}, <>1" criteria gets added.

Perhaps there is another way to accomplish this? Anything to save me from making this change tonight only to have to reverse it again in the morning :)

I look forward to / appreciate your thoughts and input.



(Ps - I hope my stakeholders don't even pop into the forumns here :)

pps - trying to edit this to include my line breaks. Apologies for the giant, wall of text

Best Answer