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!
Answers
-
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")
School of Sheets (Smartsheet Partner)
If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!