Applying Formulas to Entire Columns

Hello there,

I'm new to the community. Our company has adopted SS for projects and we're now looking to expand the use to HR, specifically starting with leave requests.

I've started a leave request sheet based on the template below, with a few adjustments geared toward my company.

https://community.smartsheet.com/discussion/here-time-keeping-smartsheet

So far, this template is a great starting point. Now I want to enhance my template by adding simple formulas to an entire column. The problem I'm coming across is that these formulas don't apply to new rows added via web forms.

For example - see image below:

I'd like my Total Days Off Request column to auto-calculate the number of days off that an individual is requesting based on the sum of the Vacation Leave | Sick Leave | In-Lieu Leave | Bereavement Leave | Parental Leave | Leave without Pay columns
- Take a look at row two where an individual has requested for Bereavement Leave (3 days) and additional Vacation Leave (2 days), a total of 5 days off requested
Second, I'd like my Remaining Eligible Vacation Days updated once the Status is changed to Approved by subtracting Current Eligible Vacation Days from Vacation Leave
- I'd like to apply a similar concept to Sick Leave as well

Perhaps you have some tricks up their sleeves or a workaround for my above roadblock?

I'm also open to any suggestions that may have worked for you and your company.

SS-Leave Request.jpg

Find more posts tagged with

Human Resources

Comments

L_123

you could just add them individually

=[Vacation Leave]@row+ [Sick Leave]@row + [In-Lieu Leave]@row + [Bereavement Leave]@row+ [Parental Leave]@row + [Leave without Pay]@row

or even use a sum formula across the range

=sum([Vacation Leave]@row:[Leave Without Pay]@row)

Or something similar

for the second part just subtract them

=[Current Eligible Vacation Days]@row - [Total Days Off Request]@row

ichen

Thanks for your reply.

That's exactly what I have set up, but I noticed that if there was a new line added (via web form) to the top of the list, the formula doesn't apply. However, if the new line is added to the bottom of the list, it appears to be working...

It's not as convenient as the leave request list begin to grow - and the need to scroll all the way to the bottom of the list as opposed to it appearing with the formulas applied at the top of the list.

It's still a work around for the time being though.

Thanks agains!

Andrée Starå

Hi,

To make it easier to handle you could use filters on the sheet or use a report.

I hope this helps you!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

eric.o

Hi Ichen,

1. If you'd like formulas to automatically add to newly added rows, Smartsheet currently automatically achieves utilizing the native tool Autofill. Autofill for formulas is applied when specific conditions exist in the sheet. If you're finding your Autofill isn't working it's likely not triggering one of the following conditions outlined here: https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

Note: You can change the location of new entries from a Form utilizing the steps outlined here: https://help.smartsheet.com/articles/522221-collect-information-form#manageform

2. Yes, Luke_TK is absolutely right in achieving the first formula calculation. The most efficient way to achieve this is by utilizing a SUM function. https://help.smartsheet.com/function/sum

=SUM([Vacation Leave]@row:[Leave Without Pay]@row)

3. If you'd like Remaining Eligible Vacation Days to update once Status is changed to Approved by subtracting Current Vacation Days from Vacation Leave, you can achieve this utilizing an IF Function. https://help.smartsheet.com/function/if

Such as this:

=IF(Status@row="Approved", [Current Eligible Vacation Days]@row - [Total Days Off Request]@row)

Cheers,

Eric

Smartsheet Support

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.