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

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

Time Off Schedule.png

Attendance Sheet.png

Find more posts tagged with

Formulas

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Getting error message in approval email
Hey, Is anyone else facing this issue in the approval notifications sent through smartsheet
Add Package Count Based on Customer
I have a sheet with a list of customers in one column, and then the following columns are City, Monday, Tuesday, Wednesday, Thursday, Friday. I need help with a formula that I can put in my sheet summary so that if the customer column says Staples (It can say this in multiple rows) that it will tell me the total package…
Automation not triggering all of the time
Hi Smartsheet community, I've always tried to answer questions but this is my first time posting. So I have formula =IF([Project Status]@row = "Complete", 1, 0) set up in the "Complete Check - Hide column" to check the box when the project status is marked complete. The project status column is tied to several other sheets…