Flag overlapping projects based on RYG ball

Dustin.Crabtree

I've looked and am unsure if smartsheet is even capable of this, but can I turn on a flag based on if a new rows start date falls in between the start date and completion date of any other row? I'm also trying to have it triggered ONLY when the complexity (RYG ball) is red.

Am I far off with this formula:

=IF(AND([Start Date]@row >= [Start Date]:[Start Date], [Start Date]@row <= [Completion Date]:[Completion Date]), IF(AND([Project Complexity]:[Project Complexity], "Red", 1)))

Paul Newcome

Hmm... I have a few thoughts on this but will have to get back to you. It is certainly interesting. I feel like will will actually end up using something more along the lines of

 

=IF(COUNTIFS(....................) > 0, 1)

 

and including all of our criteria there in the COUNTIFS. I will let you know what I come up with though.

Paul Newcome

I am not sure exactly how you are wanting to incorporate the "Red" criteria, so here are two different options.

.

If you want to flag row 1 because it has a Start Date that overlaps other dates, and row 1 is Red, then you will need to use something along the lines of...

 

=IF(AND(COUNTIFS([Start Date]:[Start Date], @cell <= [Start Date]@row, [Completion Date]:[Completion Date], @cell >= [Start Date]@row) > 1, [Project Complexity]@row = "Red"), 1)

.

If you want to flag row 1 because it has a Start Date that overlaps other dates, and at least one of those other dates is Red, then you will need to use something along the lines of this instead...

 

=IF(COUNTIFS([Start Date]:[Start Date], @cell <= [Start Date]@row, [Completion Date]:[Completion Date], @cell >= [Start Date]@row, [Project Complexity]:[Project Complexity], @cell = "Red") > 1, 1)

Dustin.Crabtree

Thank you so much for the help!



The way we are trying to incorporate the red RYG ball is to flag any new red complexity project that overlaps another red complexity project. So I think it is sort of a mix of the two formulas you provided.



Now that I see the general syntax of it I'm going to try to see what I can make work.

Again, thank you!

Dustin.Crabtree

Wanted to update this for future reference and to doublecheck that it is working the way I think it is.

But this formula seems to do the trick:

=IF(AND(COUNTIFS([Start Date]:[Start Date], @cell <= [Start Date]@row, [Completion Date]:[Completion Date], @cell >= [Start Date]@row, [Project Complexity]:[Project Complexity], @cell = "Red") > 1, [Project Complexity]@row = "Red"), 1)

Paul Newcome

Of course. The one thing I didn't account for is Red being in both places, and that's the one you needed. Hahaha. 

 

But that's exactly the way I would have combined the two. Well done. 

Dustin.Crabtree

Hi Paul,

 

Quick question now that this formula has been running for me.

We have form submissions populating to the top of the sheet, but the formula seems to flag older rows and not new submissions. Is it possible somehow to have the formula flag in the opposite direction?

 

Flipping the form and having submissions populate to the bottom is a possibility but would require quite a bit of work.

Andrée Starå

Hi Dustin,

Isn't the formula added automatically for new submissions?

If that's the case, you need to add at least two rows with the same formula pattern above the current first row to make them auto-fill.

More info: https://help.smartsheet.com/articles/1641473-auto-filling-formulas-and-formatting

Did it work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Dustin.Crabtree

Hi Andree,

 

The auto-fill is working correctly, but the formula flags older rows and not new rows. I need it to work in the reverse order, so it is only flagging new submissions. My assumption is that smartsheets formulas work from the top-down?

I ended up adding "Created:Created, Created@row = (TODAY())" using an auto-number column so it only flags new submissions as they are added.

Paul Newcome

You are correct that the formulas work from the top down (and also from left to right). 

 

I am having trouble visualizing exactly what the issue is though as it should apply to all rows regardless of top vs bottom because you are referencing entire columns and @row.

 

But it sounds like you have found yourself a solution though. I assume you have something going out as an alert as soon as a row is flagged? If not, there is the potential to miss a flag if the sheet isn't checked daily.

Andrée Starå

Ok.

Glad that you got it working!

Dustin.Crabtree

Hi Paul,

 

So for whatever reason the formula was only flagging submissions below the actual row that caused the flag, I had needed it to only flag the new row that created the overlap.

Having it only flag current/new submissions fixed it though so I believe I'm good to go now.

 

Any flagged row ends up sending an automation to the person who submitted that row letting them know it's been declined due to the overlap and it removes that row to a specific "declined projects" sheet. No need to check up on the sheet!

Paul Newcome

That's some odd behavior. At least you got it working.

 

I do like the workflow as well. Nicely done. 

Andrée Starå

I agree with Paul,

Nice workflow and use of the move row automation.