VLOOKUP Reference Vacation Calendar to Update Approver via Automated Workflow
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:
Best Answer
-
I was able to find the solution. If at first you do not succeed, try and try again! 😊
Answers
-
I was able to find the solution. If at first you do not succeed, try and try again! 😊
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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!
-
Thanks for sharing, Nicole!
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!