Health check with 3 criteria not working
Hi All, it's probably something REALLY basic for you all, but I cannot for the life of me get my Health check status to convert from Red"No"/Yellow"Hold"/Green"Yes". I have written my formula as:
=IF(ISDATE([LOA Issued]@row), "No", IF(AND([LOA Received]@row = 1) + (ISDATE([LOA Issued]@row)), "Hold", IF(AND([DRAs]@row = 1) + ([LOA Received]@row = 1) + (ISDATE([LOA Issued]@row)), "Yes")))
I've tried with and without the +. I only get that the first of my 3 criteria has been met. Please help! Thanks!!! :)
Best Answers

you need to reverse your if statement as the true statement for the "No" part is true for other criteria, and you are filtering them out prior to meeting them. There are also some syntax issues here, but I think I get the general Idea.
IF(AND([DRAs]@row = 1, [LOA Received]@row = 1, ISDATE([LOA Issued]@row), "Yes",IF(AND([LOA Received]@row = 1, ISDATE([LOA Issued]@row), "Hold",IF(ISDATE([LOA Issued]@row), "No"
you can simplify this down a bit, but I tried to keep it close to your original formula so you can understand what the syntax should be.

Thanks! But unfortunately I received the following error '#UNPARSEABLE'. What I am hoping to do is get a red/"No" indicator if a date is entered in the "LOA Issued" column... a yellow/"Hold" indicator if both a date is present and if it has been "LOA received"... and a green/"Yes" indicator if all three criteria have been met. Since the last 2 columns are checkbox indicators, I assume I need the "=1". Thanks in advance!!

Ok I misunderstood the goal. With your comment explaining exactly what you want to do, I can simplify this down quite a bit.
Keep in mind that an if statement returns a true and a false, not a true and a continue. That is if you have checked the criteria with the if statement, you don't need to check it further down the line. An example of this would be
=If(A1 <= 0, "The cell is negative or zero", if(A1< 10, "The cell is between 0 and 10","The cell is greater than or equal to 10"))
So the common indicator that is in all 3 of your checks is ISDATE([LOA Issued]@row)
=if(isdate([LOA Issued]@row),
The second indicator is [LOA Recieved}@row
=if(isdate([LOA Issued]@row),if([LOA Recieved]@row = 1,
and the last indicator is [DRAs]@row
=if(isdate([LOA Issued]@row),if([LOA Recieved]@row = 1,if([DRAs]@row = 1
Then we can start stacking our results.
=if(isdate([LOA Issued]@row),if([LOA Recieved]@row = 1,if([DRAs]@row = 1,"Yes","Hold"),"No"),"")
Answers

you need to reverse your if statement as the true statement for the "No" part is true for other criteria, and you are filtering them out prior to meeting them. There are also some syntax issues here, but I think I get the general Idea.
IF(AND([DRAs]@row = 1, [LOA Received]@row = 1, ISDATE([LOA Issued]@row), "Yes",IF(AND([LOA Received]@row = 1, ISDATE([LOA Issued]@row), "Hold",IF(ISDATE([LOA Issued]@row), "No"
you can simplify this down a bit, but I tried to keep it close to your original formula so you can understand what the syntax should be.

Thanks! But unfortunately I received the following error '#UNPARSEABLE'. What I am hoping to do is get a red/"No" indicator if a date is entered in the "LOA Issued" column... a yellow/"Hold" indicator if both a date is present and if it has been "LOA received"... and a green/"Yes" indicator if all three criteria have been met. Since the last 2 columns are checkbox indicators, I assume I need the "=1". Thanks in advance!!

Ok I misunderstood the goal. With your comment explaining exactly what you want to do, I can simplify this down quite a bit.
Keep in mind that an if statement returns a true and a false, not a true and a continue. That is if you have checked the criteria with the if statement, you don't need to check it further down the line. An example of this would be
=If(A1 <= 0, "The cell is negative or zero", if(A1< 10, "The cell is between 0 and 10","The cell is greater than or equal to 10"))
So the common indicator that is in all 3 of your checks is ISDATE([LOA Issued]@row)
=if(isdate([LOA Issued]@row),
The second indicator is [LOA Recieved}@row
=if(isdate([LOA Issued]@row),if([LOA Recieved]@row = 1,
and the last indicator is [DRAs]@row
=if(isdate([LOA Issued]@row),if([LOA Recieved]@row = 1,if([DRAs]@row = 1
Then we can start stacking our results.
=if(isdate([LOA Issued]@row),if([LOA Recieved]@row = 1,if([DRAs]@row = 1,"Yes","Hold"),"No"),"")

Yay! It works! YOU ARE AMAZING!!! THANK YOU!!! :)

Glad it worked for you :)
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.8K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.7K Ideas & Feature Requests
 142 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 297 Events
 37 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!