How to auto enter specific information based on a checkbox and specfic user?

This question is about a specific Smartsheet I'm creating for my leadership. I've created a travel calendar using one of the travel calendar templates and am editing it to fit my needs. Below is a screenshot of what I have created so far as well as what I'm trying to accomplish:

The Name - Location - Purpose column joins multiple columns using this formula:

=[Employee Name]@row + " @ " + [Store #]@row + " in " + City@row + ", " + State@row + " for " + [Business Purpose]@row

The PTO Week column I'm using to track PTO for the team. My question is, keeping in mind the formula I have in my first column, what additional formulas can I enter into my Store #, City, and State columns that will automatically enter the employee's info based on what option is selected in the Employee Name drop-down and the PTO box is selected? Is it even possible to accomplish this? I'm only using this sheet so there will be no need to pull data from an additional sheet unless that is what is needed to pull the employee information. The leadership team will only see a calendar view of travel for my team and I have a form setup so the team will rarely ever have visibility to this sheet unless they add the data directly. I'm still getting my feet wet with Smartsheet so excuse the ignorance around this.

I know this is a very specific scenario but some help would greatly be appreciated. Happy holidays and thank you for the support!

Find more posts tagged with

Accepted answers

John Shane

@Brandon R

An IF statement evaluating the check box in the PTO Week column should do what you need. The formula below should handle what you are trying to do.

=IF([PTO Week]@row = 1, [Employee Name]@row + " is on PTO", [Employee Name]@row + " @ " + [Store #]@row + " in " + City@row + ", " + State@row + " for " + [Business Purpose]@row)

Cheers,

John

All comments

Matthew J McAteer

HI @Brandon R,

It is not entirely clear what you are trying to accomplish when the PTO Week box is checked. Can you elaborate on that scenario?

Matthew

Brandon R

Sorry, I know it's a little convoluted. When PTO box is checked either in the Form or on the Smartsheet itself, I want the first column to show the associate is on PTO and not "Employee Name" @ 'Blank' in 'Blank' , 'Blank' for. That is the combined formula I showed in the original post. What logic/adjustment to my formula would I have to add to change it so when the PTO box is checked, It will just show the Employee Name with the additional verbiage of "on PTO". I know I can add an OR statement but am unsure of how I would incorporate the checkbox. This is what the sheet looks like right now:

I would like for the second row to show "Lea Atherton is on PTO" in the first column without removing the formula on the first row.

John Shane

@Brandon R

An IF statement evaluating the check box in the PTO Week column should do what you need. The formula below should handle what you are trying to do.

=IF([PTO Week]@row = 1, [Employee Name]@row + " is on PTO", [Employee Name]@row + " @ " + [Store #]@row + " in " + City@row + ", " + State@row + " for " + [Business Purpose]@row)

Cheers,

John

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.