Updating RYGB status based on date AND dropdown selection in multiple columns
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!!
Comments
-
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
Regards - JC
-
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!
-
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")))
-
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")))
-
Thank you for helping Brian (I was on vacation, hence the delay in replying ).
I tried your formula, but it says #UNPARCEABLE.
-
Can you provide a screenshot?
-
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!!
-
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.
-
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.
-
Great tip, ty
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 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!