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!!

Find more posts tagged with

Sheets

Formulas

Conditional Formatting

Project Management

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Status Log Push to Dashboard
I want to push the latest status log sheet entry to the dashboard. I have added working columns for the last date, recent value (0 or 1) and recent status (text field). The recent columns are blank except for the most recent data rows (that part of the sheet is working). I have a project metric sheet template across topics…
Calculate Project Time TOTAL for PM across aligned projects
Hi - I am trying to figure out how to create a SUM of the Total Project Time aligned to each PM for all of their projects. On my master sheet I have the Resource Type, Resource Name, and a Helper Column for Time. The Helper Time is an estimated allotted time based on the timeframe of the project. Example: <3 months = 25…
How to Create Time Duration
I have a report where I need a formula to figure out time duration. For example, employee starts at 8:00 am and ends at 9:00 am, I need the next column to tell me 1.00 to indicate he worked 1 hour. I will be using military time throughout this sheet.