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:

Find more posts tagged with

Calendar View

Formulas

Automations

Accepted answers

Nicole Whyte

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

All comments

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.