If(and with RYG buttons

Netha Lawrence

I am trying to set color buttons to the status of each row. Currently I am getting #UNPARSEABLE

My formula is:

=IF([Status]@row = "Complete", "Yellow", " ")IF(AND([Status]@row = "Pending", "Yellow", " ")IF(AND([Status]@row = "InProcess", "Yellow", " ")IF(AND([Status]@row = "In Progress", "Yellow", " ")IF((AND([Status]@row = " ", "Red")))))

Can someone help?

 

Jeremy C

Netha,

Looks like you closed off your first If statement without nesting the following If statements inside of it. From what it looks like you want to set the cell to Yellow for all status except if the status is blank is that correct?

Nic Larsen

Try this. You have a number of status set for Yellow and using the AND function but i didn't see a seperate condition. Is there another factor to consider? If not, you don't need all those AND's:

=IF([Status]@row = "Complete", "Yellow", IF([Status]@row = "Pending", "Yellow", IF([Status]@row = "In Process", "Yellow", IF([Status]@row = "In Progress", "Yellow", IF([Status]@row = " ", "Red"

Jeremy C

Tried this based on your original formula and it seemed to work using OR instead of IF(AND(

=IF(Status@row = "", "Red", IF(OR(Status@row = "Pending", Status@row = "InProcess", Status@row = "In Progress"), "Yellow", "Red"))

Paul Newcome

I see all criteria pointing to Yellow with the exception of the Status being blank. To get those same results, you could use

 

=IF(Status@row = "", "Red", "Yellow")

Jeremy C

Can't believe I overlooked the simplicity of it all... 

Andrée Starå

Easy to miss!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

It is only that simple if in fact there are only those two options. If there was a typo in the originally posted formula where one or more options should have been a different color, then going the route of the OR function would most likely be the way to go.

Netha Lawrence

That didn't work, cell says #Unparseable

Netha Lawrence

Jermey, That didn't work, the cell says #unparseable.

Jeremy C

Could you provide a little more detail of your sheet and/or possibly a screenshot? 

Netha Lawrence

Nic this worked on one of my sheets but not the other. On other sheet it leave everything blank for the cell.

 

Paul Newcome

Can you post your EXACT formula(s) that throw errors? If all options are throwing errors, it may be something as simple as a typo in a column name or something to that affect.

Netha Lawrence

Here of the snap shots.

Netha Lawrence

I found the fix!! Thanks everyone.

Jeremy C

Awesome!