Using Contact List Column Type in formula

ScreamsAtBirds

Hello,

I am trying to find a way to setup a rule that will present a user a warning if they select a team member for a task from a Contact List Dropdown on a certain day of the week. From My research I have gathered that this is not a straightforward task. Is there a way to write the formula if referencing a helper sheet though? As I also have these team members in a "roster" of sorts on another sheet. Below is the current formula I have when I thought I could reference the email from the contact dropdown.

=IF(AND(OR(WEEKDAY([Column2]@row) = "5", WEEKDAY([Column2]@row) = "6"), [Column3]@row = "HelpPlease@Smartsheet.com"), "Not Available this day", "")

Brian_Richardson

Depends how you have your roster setup, but here's some suggestions:

Roster Columns

• Team Member: contact list column with single person selected for each row
• Weekdays Available: multiple-selection dropdown with 1-7 as options (note, you need to use numbers here, if you have someone pick multiple dropdown days of the week you will need to add another lookup sheet to blend a list of numbers back into your roster)

Schedule Columns

• Team Member: contact list column, single selection
• Date Requested: date column
• Weekday: not strictly necessary, but good for troubleshooting. Formula is =WEEKDAY([Date Requested]@row)
• Unavailable?: checkbox column (I like the red flag version). Formula is =IF(CONTAINS(Weekday@row, INDEX({Weekdays Available}, MATCH([Team Member]@row, {Team Member}, 0))), false, true)

When typing out the Unavailable formula, the {Weekdays Available} and {Team Member} references are inserted by clicking "Reference Another Sheet" in the formula box that pops up. Browse to your roster sheet, then click the appropriate column and give the reference the appropriate name before hitting ok.

Now that you have an Unavailable? flag, you can use that flag/checkbox in your Conditional Formatting to highlight the row or certain columns in a color (like red). You can also initiate automations to do something like notify a supervisor or send an alert back to the requestor, or whatever you need to do.