Nested IF formula with Status colors
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"))
Best Answers
-
Try switching the Yellow and Green IF statements so that Yellow comes first.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
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.
-
When you say the yellow isn't working, is it throwing an error, or is it just not registering at all?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
#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"))
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
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!
-
Try switching the Yellow and Green IF statements so that Yellow comes first.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!