# Need formula & function for the question below

Options
edited 04/26/24

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.

• ✭✭✭✭
edited 05/07/24
Options

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!

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