If statement referencing another sheet returns invalid operation

pdv90
pdv90 ✭✭✭
edited 09/07/23 in Formulas and Functions

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

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓

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

  • Itai
    Itai ✭✭✭✭✭✭
    Answer ✓

    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🙂

    https://www.linkedin.com/in/itai-perez-740543116/

Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Answer ✓

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

  • Itai
    Itai ✭✭✭✭✭✭
    Answer ✓

    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🙂

    https://www.linkedin.com/in/itai-perez-740543116/

  • pdv90
    pdv90 ✭✭✭

    Thank you so much Eric & Itai. It's working now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!