IF/AND/OR Statement Error

John E

Alright my Smartsheet Wizards... I have another formula error that is stumping me...

I'm trying to return a Red dot if the PROJECT NUMBER in my Sheet (Sheet A) matches a cell in another sheet (Sheet B), AND any one of 5 other columns in Sheet B has a Red dot in it. Here is my current formula:

=IF(AND({Services Financial Risk Details Range 1} = [Project Number]@row, OR({Services Financial Risk Details Range 2} = "Red", {Services Financial Risk Details Range 3} = "Red", {Services Financial Risk Details Range 4} = "Red", {Services Financial Risk Details Range 5} = "Red", {Services Financial Risk Details Range 6} = "Red")), "Red", "Green")

I am getting an Invalid Operation Error, but can't find it.

Eric Law

Hello @John E

Question for you, is there a reason why you are trying to do this on the separate sheet? I recommend using a helper column in Sheet A to do the formula for the red dot, and then link that cell to Sheet B. It would be easier since you will be able to do it all within the row. If you need help with that, please post a screen shot of Sheet A with the headers.

Ryan Kramer

Let's see if this works -

=IF(AND(COUNTIF({Services Financial Risk Details Range 1}, [Project Number]@row) > 0, (COUNTIF({Services Financial Risk Details Range 2}, "Red") + COUNTIF({Services Financial Risk Details Range 3}, "Red") + COUNTIF({Services Financial Risk Details Range 4}, "Red") + COUNTIF({Services Financial Risk Details Range 5}, "Red") + COUNTIF({Services Financial Risk Details Range 6}, "Red")) > 0), "Red", "Green")

Ryan

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

John E

@Eric Law - I did as you recommended and created a Helper Column on Sheet B to give me the highest level of risk from the 5 columns I wanted to evaluate on Sheet B. Now I want to write a formula on Sheet A that looks for a identifier number from that row on Sheet A within a column range on Sheet B. If (when) it finds that Sheet A identifier number in the column range on Sheet B, I want it to return the risk level (red/yellow/green) from the Helper column on Sheet B's corresponding row to the formula cell in Sheet A. Here is what I have come up with so far, but I'm getting an error again:

=IF({Services Financial Risk Details Range 1} = [Project Number]@row, {Services Financial Risk Details Range 7} @row,"")

Eric Law

Hello @John E

I would recommend a helper column in sheet A as it is an easier formula to create. For the helper in Sheet A,

=IF([Project Number]@row = "Red", IF(COUNTIF(Column1@row:Column5@row)>0, "Red"))

Then, on Sheet B, you will just need to match.

=INDEX({Sheet A Helper}, MATCH([Project Number]@row, {Sheet A Project Number}, 0))