Creating a Formula to Return Conditional Values

nfoster9

Hello!

I'm novice when it comes to building formulas and am looking for guidance on essentially copying my conditional formatting to return values instead of highlights.

I am tracking missing trainings/policies and want a cell to return a value from a dropdown menu if a training is missing or past compliance. The tricky thing for the training is some values are dates, some are "COMPLETE".

To summarize how I want the formula to function:

IF Training Core is blank OR IF Training Refresher 1 Due is in the past AND Training Refresher 1 is blank OR IF Training Refresher 2 Due is in the past AND Training Refresher 2 is blank, return "Training". IF HR Training is unchecked, return "HR Training". IF Manager HR Training is unchecked, return "Manager HR Training". IF IT Training is unchecked, return "IT Training".

I want multiple values from the dropdown selected if multiple trainings are missing. Is this even possible with so much criteria and and this number of different possible values to be returned?

=Chris Palmer

Hello,

Something like this perhaps?

=IF(OR(ISBLANK([Training Core]@row), [Training Core]@row < TODAY()), "Training Core, ", "") +
IF(OR(ISBLANK([Training Refresher 1 Due]@row), AND([Training Refresher 1 Due]@row < TODAY(), ISBLANK([Training Refresher 1]@row))), "Training Refresher 1, ", "") +
IF(OR(ISBLANK([Training Refresher 2 Due]@row), AND([Training Refresher 2 Due]@row < TODAY(), ISBLANK([Training Refresher 2]@row))), "Training Refresher 2, ", "") +
IF(NOT([HR Training]@row), "HR Training, ", "") +
IF(NOT([Manager HR Training]@row), "Manager HR Training, ", "") +
IF(NOT([IT Training]@row), "IT Training, ", "")

To summarize:
Using checkbox columns for HR/Manager HR/IT Training, which return "true" if checked.
Date fields like "Training Core" and "Training Refresher 1 Due" need to be validated against today’s date.

nfoster9

Hi Chris,

Thank you, this is very helpful! I edited your formula a bit to fit my needs and the parts between the addition (+) symbols work independently as I want them to, but altogether they return #INVALID OPERATION.

The first part of the formula; works:

=IF(OR(ISBLANK([Training Core]@row), NOT([Training Core]@row = "COMPLETE")), "Training, ", "")

The second part of the formula; works:

=IF(AND(NOT(ISBLANK([Training Refresher 1 Due]@row)), [Training Refresher 1 Due]@row < TODAY(), ISBLANK([Training Refresher 1]@row)), "Training Refresher 1, ", "")

Together; returns error message:

=IF(OR(ISBLANK([Training Core]@row), NOT([Training Core]@row = "COMPLETE")), "Training, ", "") + IF(AND(NOT(ISBLANK([Training Refresher 1 Due]@row)), [Training Refresher 1 Due]@row < TODAY(), ISBLANK([Training Refresher 1]@row)), "Training Refresher 1, ", "")

Any advice?

=Chris Palmer

As a sanity check we should confirm any column that contains dates have a property set to "Date". Otherwise Smartsheet may recognize the dates as "Text".

If I'm understanding your needs correctly, this formula returns one of the following based on unmet training requirements:

"Training, Training Refresher 1" Core is incomplete and refresher 1 is overdue

"Training" Core is incomplete, refresher 1 is not (yet) due or is complete

"Training Refresher 1" Core is complete, refresher 1 is overdue

"" Both core training is complete and refresher 1 is not overdue

=IF(OR(ISBLANK([Training Core]@row), [Training Core]@row <> "COMPLETE"),
IF(AND(NOT(ISBLANK([Training Refresher 1 Due]@row)), [Training Refresher 1 Due]@row < TODAY(), ISBLANK([Training Refresher 1]@row)),"Training, Training Refresher 1","Training"),
IF(AND(NOT(ISBLANK([Training Refresher 1 Due]@row)), [Training Refresher 1 Due]@row < TODAY(), ISBLANK([Training Refresher 1]@row)),
"Training Refresher 1",""))

Let me know how that goes.

nfoster9

Hi Chris,

Yes, the columns are set to "Date".

All of the criteria you listed is accurate except that the refreshers are only relevant once the core training has been completed. Therefore:

"Training" Core is incomplete, refresher 1 is not (yet) due or is complete

"Training Refresher 1" Core is complete, refresher 1 is overdue

"" Both core training is complete and refresher 1 is not overdue or is complete

I tried your suggested formula and it works up until both the core and refresher have been completed. Do you have a recommendation?

This is also only the start of the formula; I was slow-rolling the build-out so as not to overwhelm myself. I'll need to incorporate the remaining columns in my original post (Refresher 2, Refresher 3, HR Training, Manager HR Training, IT Training) eventually.