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.
Comments
-
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
-
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!
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives