Formula help - VLOOKUP from another sheet with deduction based on other criteria

Jason@JTS

Hello All,

I am hoping someone can help me. I am trying to make an attendance sheet that will automatically deduct hours from the user's allotment based on manager approval and to also deduct from the correct type of hours (we have PTO hours and Vacation hours, each in different columns).

I have a master sheet containing all employees and how many hours they have of each type (PTO & Vacation) called JTS Attendance Sheets.  I have another sheet called JTS Time Off Schedule where it contains the time off requests and each request has to be approved by the manager of the department.  I was wanting the column on the time off requests sheet to show the number of hours available for the type of time off and then if it has been approved, then deduct the hours from the appropriate column on the JTS Attendance Sheets and reflect the new amount.

This is what I was attempting to do.  It does not work, so there must be an issue with my formula.

=VLOOKUP([Employee Name]@row, {JTS Attendance Sheets Range 1}, 6, false) IF([Type of Time Off]110="PTO" AND [MarkedAsApproved]@row= 1, VLOOKUP([Employee Name]@row, {JTS Attendance Sheets Range 1}, 6, false) - [# of Hours]@row

eric.o

Hello,

 

It appears from the formula provided that an error is occurring because the AND function is out of place. You'll want to place the AND function after the IF Function similar to the below. 

 

=IF(AND([Type of Time Off]110="PTO", [MarkedAsApproved]@row = 1), VLOOKUP([Employee Name]@row, {JTS Attendance Sheets Range 1}, 6, false) - [# of Hours]@row)

 

If the issue continues after performing the above I'd recommended connecting with us via our support link here: https://help.smartsheet.com/contact

We'd be happy to further investigate. 

 

Cheers, 

Eric  

Smartsheet Support

Jason@JTS

Hello, Eric.

This only partially worked.  

I attempted the support link, but they just sent me generic links to help with formulas and templates and suggested that the community is able to provide real-world examples.

There are multiple things that did not work.

1. It subtracted hours on the Time Off Schedule sheet for non "PTO" hours, when it should only apply when both the Type of Time Off is "PTO" and the MarkedAsApproved column is checked. The Type of Time Off can contain other text, in which case, it should not subtract hours.

2. When using the same formula for Type of Time Off of "Vacation" and placing it in the Vacation Remaining field, it did not work.

3. It does not update the hours on the referenced sheet. 

Thanks for the help.

eric.o

Hello,

Yes, the community is a great place to test real-world examples and find the syntax for formulas you're looking to create based on your desired goal. The link provided above should be utilized for troubleshooting formulas we already have that are not working. 

 

1. The AND Function formula provided above should only calculate if both conditions are met, that is likely why it worked for "Vacations". If you're finding this isn't working as desired for "PTO" it may be the field contains PTO unknowingly. You'll want to investigate the cells being referenced that shouldn't be to see if they contain one of the criteria. 

 

3. If by "it does not update the hours on the referenced sheet" you're referring to a cell-linked cell from the formula sheet you may be experiencing a browser issue. Please log out of all instances of Smartsheet, clear your cache/cookies for 'All Time' ensuring all advanced settings are checked, and try again. 

 

If either issue continues after performing the above you will want to fill out the above support contact link. Please ensure to link this community link so they're aware of the steps we've taken. 

 

Cheers, 

Eric  

Smartsheet Support