If statement referencing another sheet returns invalid operation

Options
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 ✓
    Options

    @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 ✓
    Options

    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

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

    @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 ✓
    Options

    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/

  • pdv90
    pdv90 ✭✭✭
    Options

    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!