Column IF AND Function Help

Hi all!

I cannot seem to get my IF AND statement to work and would sincerely appreciate some guidance!

I am attempting to alter/update a cell on one sheet that acts as a matrix for employee training which is based upon information from a reference sheet. Essentially, a row in the reference sheet is populated every time a training session has been completed, when this occurs I want the IF function to recognize what training occurred, whom it occurred for to then update the matrix accordingly.

Below is a visual example of what I want to reference in the initial sheet that will then update the matrix to the corresponding skill.

For example, for the 'Picking' Skill for Employee Bob, the current function I have is =IF(AND([Employee Name]:[Employee Name] = "Bob", [Skill Trained]:[Skill Trained] = "Picking", [Date Training Completed]:[Date Training Completed] = <>""), "Completed", "Not Started")

Would what I am trying to do be something that would function better utilizing an IF and VLOOKUP function?

Thank you!

Best Answer

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @zbr

    Because you're using a multi-select drop down column for the Skill Trained column you'll need to use an IF CONTAINS formula.

    In the Skill Trained column, it looks like you're only listing the skill there if it's been completed. Is that correct? If so, then you can do away with including Date Training Completed in your formula.

    For the Putaways column, how are you determining that skill is In Progress for Frank and Steve? If the 'In Progress' isn't shown somehow in the main table, there's no way to write a formula for that to be automatically set.

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @zbr

    Because you're using a multi-select drop down column for the Skill Trained column you'll need to use an IF CONTAINS formula.

    In the Skill Trained column, it looks like you're only listing the skill there if it's been completed. Is that correct? If so, then you can do away with including Date Training Completed in your formula.

    For the Putaways column, how are you determining that skill is In Progress for Frank and Steve? If the 'In Progress' isn't shown somehow in the main table, there's no way to write a formula for that to be automatically set.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!