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
Project Manager | Transformation Department
Gong cha
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
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
Project Manager | Transformation Department
Gong cha
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
-
Thank you so much Eric & Itai. It's working now
Help Article Resources
Categories
Check out the Formula Handbook template!