IF AND statement across sheets


Hey everyone!

I am trying to update a status ball color if it is present in our review sheet. When a material is added to our master review sheet, I want the status ball to change color to show that it is in review. I have tried to research this and tried multiple different calculations and for some reason I cannot get it to work with referencing a different sheet. I want it to look at the test and the lot number to then populate the appropriate color status ball. See below for my current calculation. The error it gives back is #INVALID OPERATION.

=IF(AND({Final, Stability, Retest Review Range lot} = [Lot #/QC ID]@row, {Final, Stability, Retest Review Test} = "Appearance"), "Green", "Blue")

Any help would be greatly appreciated!


  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    edited 07/20/20

    I think the problem is that you are selecting entire ranges and comparing them to a single cell value. For example {Final, Stability, Retest Review Range lot} = [Lot #/QC ID]@row. If you nest COUNTIF within your AND statements you can check the entire range to see if the value in question is found.

    Try using this:

    =IF(AND(COUNTIF({Final, Stability, Retest Review Range lot}, [Lot #/QC ID]@row)>0, COUNTIF({Final, Stability, Retest Review Range lot}, [Lot #/QC ID]@row)>0), "Green", "Blue")

    👨🏼💻 Dan Palenchar | School of Sheets Solutions Consulting | Smartsheet Aligned Gold Partner

    If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome.

    PS - If you have a follow up response use @Dan Palenchar so I get notified of your reply!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!