IF AND statement across sheets

Brianna Kempfer

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

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