VLOOKUP Reference Vacation Calendar to Update Approver via Automated Workflow

Nicole Whyte

Hi, I have an employee Vacation Planner sheet with a status column/formula that indicates whether an individual is currently on "PTO" based on today's date and whether it falls in the range of their vacation's start and end date.

In a separate Smartsheet, we will be managing service tickets and certain types of requests require approval for which I've set up automated approval workflows. I would like this "Service Ticket" sheet to reference the Vacation Planner sheet to match the "Approval 1 Contact" against the "Name" in the Vacation Planner column and return the "PTO" value from the Vacation Planner Status column to the "Approval 1 Out of Office" column IF the status column is showing "PTO". "Approval 1 Out of Office" should be blank if no "PTO" in Status. If "PTO" returns, the workflow will push to the back-up approver.

Having difficulty with the formula and appreciate any help.

Service Ticket Tracker:

Nicole Whyte

I was able to find the solution. If at first you do not succeed, try and try again! 😊

Nicole Whyte

I was able to find the solution. If at first you do not succeed, try and try again! 😊

Genevieve P.

Hi @Nicole Whyte

I'm glad to hear you found a solution! Would you mind posting the formula you used? It may be helpful for other members who have a similar question.

Cheers,

Genevieve

Nicole Whyte

Thanks, Genevieve! My solution was slightly different than what I originally envisioned and submitted to the Community; however, I am happy to share what worked for me in this situation. On the vacation planner, I have a column with a formula to indicate if an individual is actively on PTO: =IF(AND([Today's Date]@row >= [Start Date]@row, [Today's Date]@row <= [End Date]@row), "PTO"), and a column that fills in the email address of that individual only if they are on PTO: =IF([PTO Status]@row = "PTO", [Email Address]@row).

On the service ticket SmartSheet, I added a column for "approver 1 out of office" with the following formula: =VLOOKUP([Approver 1 Contact]@row, {Vacation Planner Range 1}, 1, false). If no match, the approval workflow goes to approver 1. If there is a match, the workflow kicks over to the back-up.

This is working for me; however, if anyone sees a hole in this approach or has a more efficient way to do it, I'm all ears!

Genevieve P.

Thanks for sharing, Nicole!