Applying Formulas to Entire Columns

ichen
ichen
edited 12/09/19 in Smartsheet Basics

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:

  1. 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
  2. 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

Comments

  • L_123
    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

  • 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å
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • eric.o
    eric.o Employee

    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