IF Formula Cross Reference

Jackson Pavelka

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!

Paul Newcome

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")

Paul Newcome

Are you able to provide some screenshots of both sheets as well as more details such as is this row specific, or are you looking for when every row in the column on the other sheet is marked as "Complete"?

Jackson Pavelka

Hi Paul,

I can definitely do that.

This is the sheet I want to reference:

If the CBCT Install Status is "Completed", then I want the CBCT Installed? column in the sheet I am trying to create to populate with "Yes". If it is anything other than completed, it should populate with "No"

Paul Newcome

Are you basing this on the Office Number?

Jackson Pavelka

Yes, I am.

Paul Newcome

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")