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
-
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
-
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"?
-
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"
-
Are you basing this on the Office Number?
-
Yes, I am.
-
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")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!