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!

Find more posts tagged with

Accepted answers

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

All comments

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:

Screen Shot 2021-04-29 at 9.36.35 AM.png

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"

Screen Shot 2021-04-29 at 9.35.50 AM.png

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.