Nested IF formula with Status colors

Vuckecucke

i am using a nested if formula, with HAS and a status ball colors.

If a person is trained on a SOP-Green (crosslinking data from sheet 1)

If not -Red (crosslinking data from sheet 1)

If author of the SOP- Yellow (crosslinking data from sheet 2)

Here is the current f-la, that works for Green and Red (same sheet), NOT for the Yellow status:( different sheet)

=IF(COUNTIFS({Copy of Copy of FOR902A Test 1 Name}, [Employee Name (First,Last)]@row, {FOR902A Test 1 Doc.Ttile}, HAS(@cell, "SOP810.03 Receiving Procedure")) > 0, "Green", "" + IF(COUNTIFS({Authors}, [Employee Name (First,Last)]@row, {SOP name}, HAS(@cell, "SOP810.03 Receiving Procedure")) > 0, "Yellow", "Red"))

Paul Newcome

Try switching the Yellow and Green IF statements so that Yellow comes first.

Paul Newcome

Nested IFs read from left to right and stop on the first true value. I made the assumption that yellows are also in the green list. If it is in the green list, it will stop evaluating there because it is true.

Order of priorities depends on what you need for your particular use case. If you want something to override something else, it should come first.

Jason Tarpinian

Try updating to this following:

=IF(COUNTIFS({Copy of Copy of FOR902A Test 1 Name}, [Employee Name (First,Last)]@row, {FOR902A Test 1 Doc.Ttile}, HAS(@cell, "SOP810.03 Receiving Procedure")) > 0, "Green", IF(COUNTIFS({Authors}, [Employee Name (First,Last)]@row, {SOP name}, HAS(@cell, "SOP810.03 Receiving Procedure")) > 0, "Yellow", "Red"))

If I'm reading it right, you don't want to add the second IF, you just want it to be the start of the previous IF false statement. So the "" + in the formula is not needed.

Vuckecucke

thanks. I tried this and was getting #incorrect. now what worked to return the Yellow was having>1not>0 for"Green", but this made all the green change to red :(. ill have to see how the count works with nested if when cross-referencing multiple sheets.

Paul Newcome

When you say the yellow isn't working, is it throwing an error, or is it just not registering at all?

Jason Tarpinian

#INCORRECT ARGUMENT SET means that your ranges are different sizes. Edit your ranges to make sure they contain the same number of rows (or select the header to include the entire column)

I was able to recreate this formula to have it work

=IF(COUNTIFS({Copy of Copy of FOR902A Test 1 Name}, [Employee Name (First,Last)]@row, {FOR902A Test 1 Doc.Ttile}, HAS(@cell, "SOP810.03 Receiving Procedure")) > 0, "Green", IF(COUNTIFS({Authors}, [Employee Name (First,Last)]@row, {SOP name}, HAS(@cell, "SOP810.03 Receiving Procedure")) > 0, "Yellow", "Red"))

Vuckecucke

I copy pasted your formula and its not registering the yellow at all. My sheet 1 is list of employee's name( lets call it y axis) and than a drop down list of sops trained on (x axis). All info collected via forms.

My authorship sheet(manually entered) is a list of Sop titles ( Y axis), and authors name (contact list collum) x axes.

this also answers Pauls question. thanks!

Paul Newcome

Try switching the Yellow and Green IF statements so that Yellow comes first.

Vuckecucke

It working!! thanks, why would this make it work though?

if i wanted to include additional condition for the blue, would there be a specific order of priorities by color?

Thank you.

Paul Newcome

Nested IFs read from left to right and stop on the first true value. I made the assumption that yellows are also in the green list. If it is in the green list, it will stop evaluating there because it is true.

Order of priorities depends on what you need for your particular use case. If you want something to override something else, it should come first.

Vuckecucke

Thank you!

Paul Newcome

Happy to help. 👍️