If statement referencing another sheet returns invalid operation
So I am using this:
=IF({Master Bookings Sheet-V2.0 Range 1} = [Cutting Room Number]@row, "Unavailable", "Available")
Above is the sheet where I'm checking the value of "Cutting Room Number" from another sheet called "Master Bookings Sheet V2.0". I have to check: if the value in "Cutting Room Number" in the above sheet is mentioned in the referenced sheet then "Unavailable" else "Available".
It's supposed to be a simple IF but it returns #INVALID OPERATION
Any help is appreciated.
Best Answers
-
@pdv90 Your formula isn't working because you are comparing an array to a single cell. You can try the following
=IF(CONTAINS([Cutting Room Number]@row, {Master Bookings Sheet-V2.0 Range 1}), "Unavailable", "Available")
-
Hey @pdv90,
I think you should try and use COUNTIF within the IF so then if the COUNTIF returns anything above 0 it will show as Unavailable.
The reason the formula is not working is that IF doesnt know how to compare a range to a cell.
You can also use Vlookup a similar way within the IF.
Let me know if you need help with writing the formula.
Itai Perez
Reporting and Project Manager
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
Answers
-
@pdv90 Your formula isn't working because you are comparing an array to a single cell. You can try the following
=IF(CONTAINS([Cutting Room Number]@row, {Master Bookings Sheet-V2.0 Range 1}), "Unavailable", "Available")
-
Hey @pdv90,
I think you should try and use COUNTIF within the IF so then if the COUNTIF returns anything above 0 it will show as Unavailable.
The reason the formula is not working is that IF doesnt know how to compare a range to a cell.
You can also use Vlookup a similar way within the IF.
Let me know if you need help with writing the formula.
Itai Perez
Reporting and Project Manager
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
-
Thank you so much Eric & Itai. It's working now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!