Forms and Formulas - Form add a row to top but formula does not include

SueinSpain

I have a smartsheet that I want to use for team holiday requests.The team would fill in a form that add their request to the top of the smartsheet.

Further down the smartsheet is a summary, by team member, of their total holiday allowance, calculated by formula of the number of days requested (and approved) and number of days left to book.

My issue is that every time the form add a row at the top, the formula is changed and starts one row down, therefore not including the new row,

so before any form is used to add a line - the formula is looking at rows 1 to 15

after using the form to insert a new row - the formula is looking at rows 2 to 16 and ignoring row 1

Ideally i would like data capture to be by form as then the team members would only have viewer access to see and not be able to update themselves - updates are restricted to the manager with editor access.

Am I being stupid, missing a trick in the formula or??????

Hope you can help

Andrée Starå

Hi,

Try something like this. This formula will look at the range of the column so you don't need to think about the row numbers.

=SUMIFS(Duration:Duration; [Team Member]:[Team Member]; =[Team Member]@row; Approved:Approved; "Yes")

The same version but with the below changes for your and others convenience.

=SUMIFS(Duration:Duration, [Team Member]:[Team Member], =[Team Member]@row, Approved:Approved, "Yes")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

Did it work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi,

Try something like this. This formula will look at the range of the column so you don't need to think about the row numbers.

=SUMIFS(Duration:Duration; [Team Member]:[Team Member]; =[Team Member]@row; Approved:Approved; "Yes")

The same version but with the below changes for your and others convenience.

=SUMIFS(Duration:Duration, [Team Member]:[Team Member], =[Team Member]@row, Approved:Approved, "Yes")

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma."

Did it work?

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

SueinSpain

Andree you are an absolute genius - it has fixed it 😍

Andrée Starå

Haha!

Excellent!

Happy to help!