Need formula & function for the question below

Options

If I apply for PTO, I want to make sure it's getting subtracted from the "WR Balance Before Application" and it would be straight subtraction selecting cells & it's sorted. The main formula I am looking for is, If I apply again, my "WR Balance Before Application" should be the latest "WR Balance Left After Application" from my last entry. How can I do that? Immediate help would be a great help and appreciated.

Answers

  • PCG Sam Harwart
    PCG Sam Harwart ✭✭✭✭
    edited 05/07/24
    Options

    Hi @Neeraj Chavan ,

    Perhaps you could have [WR Balance Before Application] be the total WR balance less any [Approved in HRMS] and the [WR Balance Left After Application] could be the total WR balance before application less any pending requests. Where does the WR balance come from?

    1. [WR Balance Before Application]: =[WR Balance]@row - SUMIF([Approved in HRMS]:[Approved in HRMS], 1, [WR Applied]:[WR Applied])
    2. [WR Balance Left After Application]: =[WR Balance Before Application]@row - SUMIF([Approved in HRMS]:[Approved in HRMS], 0, [WR Applied]:[WR Applied])

    What formulas are you using in there now?

    Cheers,
    Sam

    -
    primeconsulting.com

    Cheers,
    Sam

    -
    primeconsulting.com | Smartsheet's 2023 Partner of the Year for North America
    Want to chat about a Smartsheet problem you're facing? Grab time on my calendar here: Schedule a Discovery Call!

  • Brian_Richardson
    Brian_Richardson Overachievers
    Options

    If I'm understanding this correctly, you expect multiple rows for one person? So in the example you may have 1 row for Agrima with WR Balance Before Application = 10 and WR Balance After Application = 9.5 then a second row further down where you want Agrima to show WR Balance Before Application = 9.5 (balance from earlier row) and then subtract a value from that?

    I'll show how to do this below, but I'd actually suggest restructuring to make this simpler. If you have 1 sheet with all the names on it, and a separate sheet with the actual PTO changes (adds/removes) on it, then it becomes simple to have a cross-sheet reference formula that simply adds the value for each person.

    = SUMIF({Name} , Name@row , {WR Applied} )

    If instead you want to build a total down the sheet, you can do this using a couple of helper columns.

    Setup an AutoNumber column called "Auto"

    Setup a Text/Number column called "WR Applied to Date" . In this column add the following column formula:

    =SUMIFS([WR Applied]:[WR Applied], Auto:Auto, <=Auto@row, Name:Name, Name@row)

    This will add up a total WR applied, up to and including that row, but not the rows further down. You can then subtract that total applied from your balance.

    BRIAN RICHARDSON | PMO TOOLS AND RESOURCES | HE|HIM

    SEATTLE WA, USA

    IRON MOUNTAIN

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!