Formula for Accrual Management

rer9080

Hello experts,

I'm seeking assistance in setting up a Smartsheet formula to manage vacation requests for my team. Here's the scenario:

I have two sheets in my Smartsheet workspace:

1. Team PTO & Absence Management Sheet: This sheet contains all the requests for time off submitted by employees via a form and is shared as a Calendar View-Only dashboard where employees can request time off via a Smartsheet form. It includes columns such as "Employee Name", "Type of Absence", "Hours Needed", and "Approved".
2. Staff Accruals - Vacation Sheet: This sheet tracks the vacation accruals for each employee. It includes columns such as "Employee Name", Accrual Beginning", "Approved Hours" and "Hours Remaining".

Objective:

I want to create a formula in the "Staff Accruals - Vacation" sheet - to automatically sum up the "Hours Needed" for approved vacation requests from the "Team PTO & Absence Management" sheet and add them to each employee's "Approved Hours" Column.

Criteria for Summing Hours Needed:

• The "Type of Absence" column must be "Vacation".
• The "Approved" column must be checked.
• The employee names must match between the two sheets. (Names are from a contact list)

Request:

I'm reaching out to the Smartsheet community for assistance in creating a formula that dynamically sums up the hours needed based on the specified criteria for each employee and updates their vacation accruals accordingly in the "Staff Accruals - Vacation" sheet. Any guidance, suggestions, or solutions would be greatly appreciated.

Thank you in advance for your help!

KPH

Hi @rer9080

Thank you for a very clear brief. I think the formula you need is a SUMIFS using cross-sheet references.

The SUMIFS allows you to SUM the values in a column IF multiple pieceS of logic are true.

I will walk you through it.

I've mocked up a Team PTO & Absence Management Sheet to mimic yours, which looks like this:

Tip - I suggest you make the Type of Absence on sheet 1 a restricted single select dropdown (if you haven't already) to stop people from misspelling "Vacation" - which will prevent it from matching.

I've also mocked up the Staff Accruals - Vacation Sheet and popped in a simple formula to calculate the balance. I think this is as far as you have got.

So now, you need a formula for that middle column.

Start with =SUMIFS(

And then for the range of things to SUM we need to Reference Another Sheet. Click on that link and set up a cross-sheet reference:

• Find your Team PTO sheet in the tree on the left
• Give the reference a meaningful name in the box at the top. I used the sheet name and then the column name
• Select the column for hours needed
• Click Insert Reference

The name of your cross-sheet reference will now appear in your formula:

Now, type a comma and then click on the Reference Another Sheet link again to set up the cross-sheet reference for the column that contains the Employee Name.

Again this will appear in your formula.

Enter another comma, followed by [Employee Name]@row

Your formula will look something like this (the reference names may be different).

=SUMIFS({Team PTO & Absence Management: Hours}, {Team PTO & Absence Management: Employee}, [Employee Name]@row)

This will SUM the values in {Team PTO & Absence Management: Hours} where the value in {Team PTO & Absence Management: Employee} equals the value in Employee Name on the current row in the current sheet. You can drag this formula down to see it work. Matilda had two entries in the PTO list and these are combined here.

Now we need a second range and criteria to check the type is Vacation. Add a comma to the end of the formula (before the closing parenthesis) and enter another cross-sheet reference. Then enter another comma followed by "Vacation" as the criteria. Your formula will look something like this:

=SUMIFS({Team PTO & Absence Management: Hours}, {Team PTO & Absence Management: Employee}, [Employee Name]@row, {Team PTO & Absence Management: Type}, "Vacation")

And your grid now looks like this:

James' leave is not listed as his is not Vacation.

Now, repeat that step for the Approval column. You can use 1 or true to indicate that you only wanted checked boxes. The formula will look like this:

=SUMIFS({Team PTO & Absence Management: Hours}, {Team PTO & Absence Management: Employee}, [Employee Name]@row, {Team PTO & Absence Management: Type}, "Vacation", {Team PTO & Absence Management: Approved}, 1)

or

=SUMIFS({Team PTO & Absence Management: Hours}, {Team PTO & Absence Management: Employee}, [Employee Name]@row, {Team PTO & Absence Management: Type}, "Vacation", {Team PTO & Absence Management: Approved}, true)

And now the result will be:

George's leave is not appearing as his is not approved.

I hope this helps, let us know if you have any problems.