If statement referencing another sheet returns invalid operation
So I am using this:
=IF({Master Bookings SheetV2.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 SheetV2.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/itaiperez/
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 SheetV2.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/itaiperez/

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!