IF Formula Cross Reference

I am trying to write a formula that references another sheet and returns a "Yes" or "No" to the column I am putting the formula into. I am trying:

=IF({CBCT Implementation Schedule 2021 Range 1} = "Completed", "Yes")

Essentially, if the column on the reference sheet equals "Completed", the formula would return a "Yes" value to my cell. I am getting an "Invalid Operation" error.

I think I am a little foggy this morning due to the weather. Thanks in advance for any assistance!

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. First we will use an INDEX/MATCH to pull the Install Status from the first sheet.

    =INDEX({First Sheet Install Status Column}, MATCH([#]@row, {First Sheet Office Number Column}, 0))


    Then we put that in an IF to say that if it equals "Complete", output "Yes".

    =IF(pull_status_formula = "Complete", "Yes")


    =IF(INDEX({First Sheet Install Status Column}, MATCH([#]@row, {First Sheet Office Number Column}, 0)) = "Complete", "Yes")

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!