Mary Ayers
Mary Ayers ✭✭✭✭
edited 09/20/20 in Formulas and Functions

I'm trying to create a formula for an on-boarding tracking sheet.

-There is one sheet for each new hire. The first row contains info about the new hire, including a column *Position*, which has a single-select dropdown with five position types: "Staff", "Academic Staff", Temp/contingent", "Postdoc" "Faculty".

-Remaining rows are lists of tasks.

-The *Required for Positions* column of the task rows is a multi-select drop down of positions for which that task may or may not be required: "Staff", "Academic Staff", Temp/contingent", "Postdoc" "Faculty".

-I would like to put a formula in the *Status* column that looks and the *Position* cell in Row 1: $[Position$]1 and checks it against the items selected in the *Required for Positions* column. If there is a match, *Status* should default to "Not Started". If there is no match, *Status* should default to "Not Required" (to which I then set up conditional formatting that provides a visual cue that this task is not required for this employee).

I have entered a formula into each of the *Status* cells but that is cumbersome and the formula would have to change each time the requirements change. Here's an example of a task that is required only for "Faculty" or "Staff" positions: =IF(OR($Position$1 = "Faculty", $Position$1 = "Staff", $Position$1 = "Academic Staff"), "Not started", "Not Required")

I know there's got to be better way but I haven't been able to find an example. Hoping one of you formula-gurus can help! Many thanks in advance! 🙂

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!