Updating RYGB status based on date AND dropdown selection in multiple columns

Alli J

Hello!

I would like for the RYGB status to be updated based on BOTH a due date (I already know how to do the status based on only due date), AND on the selection of a drop down menu in 5 other columns.  

So:

Red is past due AND "completed" or "N/A" are not selected in the drop down menu in multiple columns.

Green is past due AND/OR "completed" or "N/A" are selected in the drop down menu in multiple columns.

Yellow is due today AND "completed" or "N/A" are not selected in the drop down menu in multiple columns.

Blue is in progress before due date (unless green applies).

 

Is this possible??

 

Thank you!!

 

Jeremy C

I ran across a similar post that may provide some help. Sounds like a nested if statement would be a start. 

https://community.smartsheet.com/discussion/rygb-balls-based-due-date-status

 

Alli J

Thank you Jeremy! I'll see what I can make of that - it is indeed a complicated formula and I'm a beginner, but I'm sure I can figure it out eventually!

Brian W

It sounds like this is logic you want to use:

If all 5 columns contain Completed or N/A, then Green, otherwise...

If Due Date is in the past, "Red"

If Due Date is today, "Yellow"

All other cases "Blue"

If your 5 columns are contiguous, your formula should look something like this:

=IF(COUNTIF([Column1]@row:[Column5]@row, OR(@cell = "Completed", @cell = "N/A")) = 5, "Green", IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row = TODAY(), "Yellow", "Blue")))

Alli J

Yes, that's exactly what I want Brian! ..... I am not at all sure what to do with that formula you kindly wrote out though... I've tried it in a few different ways, but I'm obviously missing something. Here is what I've tried with real info, which I KNOW is wrong, lol. Please, what am I missing? I think it's a lot!

 

=IF(COUNTIF(Barry2:Dianne2, OR(Barry2:Dianne2= "Done", Barry2:Dianne2="N/A")) = 5, "Green", IF([Due Date]2 < TODAY([Due Date]2 = TODAY, "Yellow", "Blue")))

 

Brian W

I think you're close. Try it like this in row 2:

=IF(COUNTIF(Barry2:Diane2, OR(@cell = "Done", @cell = "N/A")) = 5, "Green", IF([Due Date]2 < TODAY(), "Red", IF([Due Date]2 = TODAY(), "Yellow", "Blue")))

Brian W

FYI, @cell is used when a logical expression checks a group of cells. For example, to check how many times Done appears in this group, you would use:

=COUNTIF(Barry2:Diane2, @cell = "Done")

Alli J

Thank you for helping Brian (I was on vacation, hence the delay in replying ).

 

I tried your formula, but it says #UNPARCEABLE.  

Brian W

Can you provide a screenshot?

Alli J

Here is a screenshot of the sheet. I used your formula for row 2 (row 1 just has a simple formula that only involves the due date). Thanks!!

Brian W

Oh, I think I spelled Dianne wrong. Try this:

=IF(COUNTIF(Barry1:Dianne1, OR(@cell = "Done", @cell = "N/A")) = 5, "Green", IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", "Blue"))

Paul Newcome

If I may make a suggestion... When looking across a row like that, I recommend using @row instead of the actual row number. That provides one less possibility for a typo and one less chance for any kind of auto-fill to go wonky on you.

Alli J

Ah ha! The new formula (with dianne spelled properly - sorry I didn't notice that myself!) has worked on some level. The green status isn't working though. I see that when I make the due date passed, and everything is marked done, the dot is still red, as though it's overdue and not completed. It should be marked green... Green doesn't seem to work in any case. Like if the due date is still in the future, but everyone is marked done, it should turn green then. But it just stays blue (in progress) until the due date passes, and then it turns red.... I'm thinking it's actually not really reading the done/N/A choices at all - all the colours seem to be changing only based on the date.

(can't tell you how much I appreciate your help!)

Oh, and I am in Canada.

Alli J

Great tip, ty

Brian W

Okay. The problem now is that there are 6 columns, not 5. Try this:

=IF(COUNTIF(Barry@row:Dianne@row, OR(@cell = "Done", @cell = "N/A")) = 6, "Green", IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", "Blue"))

Alli J

D'oh! Sorry, yeah, 6 columns - I must have added one and then forgotten.

Well Brian W, you are my hero.   There is no way I'd have ever figured this out on my own obviously - thank you thank you thank you! It's working perfectly, and I also learned a lot.