Applying Formulas to Entire Columns

ichen · 11/09/18

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

L_123 · 11/16/18

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 · 11/19/18

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å · 11/19/18

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 · 11/29/18

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

Applying Formulas to Entire Columns

Comments

Categories