How to ensure top line formulas calulate when rows are added

GillianMcGovern · 03/28/22

Hi Everyone I am reaching out as unsure how to do this. I am using a smartsheet form to enter holidays for staff members. I want to ensure my parent summary calculations update when rows are added. At present the forumula I am using to calculate forecasted holidays are as follows....... but when rows are added it doesn't update. I have attached a copy of the sheet I am using

=SUMIFS([No of Days]4:[No of Days]5, [Leave Type]4:[Leave Type]5, OR(@cell = "Holiday", @cell = "Statutory Holiday"))

Andrée Starå · 03/28/22

@GillianMcGovern

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå · 03/28/22

Hi @GillianMcGovern

I hope you're well and safe!

There's, unfortunately, no way to have the rows added to the right section, so you'd need to move the rows manually after submission.
As a potential workaround, you could create a report instead and use the Grouping/Sum feature to get the totals.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

GillianMcGovern · 03/28/22

Hi Andre I have found a way to move the data by sorting and it sorts to the appropriate section but can't seem to get the formula to update when the additional row item is added?

GillianMcGovern · 03/28/22

@Andrée Starå - it also needs to be a sheet to use resource manager add on

Andrée Starå · 03/28/22

https://community.smartsheet.com/discussion/comment/324197#Comment_324197

~~What do you mean? Can you elaborate?~~ I think I know what you mean, you're sorting the sheet, and then it's grouped in the correct section, and I think your issue is that it still isn't included as a child row to the section parent, and that is what you can't automate.

Make sense?

GillianMcGovern · 03/28/22

Hi Yes that's correct but even when I indent as a child the parent row doesn't update my formula above

Andrée Starå · 03/28/22

@GillianMcGovern

Yes, now I see the issue.

You'll need to use something like this instead for the formula.

=
SUMIFS(CHILDREN([No of Days]@row), 
CHILDREN([Leave Type]@row), OR(@cell = "Holiday", @cell = "Statutory Holiday"))

Did that work?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

GillianMcGovern · 03/28/22

Brilliant that's the one !!! many thanks Andree

Andrée Starå · 03/28/22

@GillianMcGovern

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

How to ensure top line formulas calulate when rows are added

Best Answer

Answers

Help Article Resources

Categories