Collecting earliest date based on certain criteria

Jeffrey Homme

Hey Smartsheet,

I'm trying to pull the earliest date that a status symbol was Red or Yellow unless that date is followed by a Green status symbol and then a Red or Yellow status symbol. In that scenario, I would want the Red/Yellow date after the Green date to be shown in the Roll-up Sheet.

In the example below, the Roll-Up Sheet should be showing 11/24 instead of 11/2 but I cannot find out what formula (or if its even possible).

Data Table

Roll-up Sheet

Thank you

Leibel S

Jeffery,

Create a helper column called ROW#. It should be a Column formula: =MATCH([Auto-Number]@row,[Auto-Number]:[Auto-Number],0).

Then your formula for pulling the above date would be:

=MIN(COLLECT(Date:Date,[ROW#]:[ROW#],>MAX(COLLECT([ROW#]:[ROW#],[Status]:[Status],"Green"))))

Jeffrey Homme

@Leibel S

That worked, thank you!! One additional question.

For that formula , what statement would I add to have it pull based on the ID in my roll-up sheet?

In the example below the formula you provided would pull 12/2/20 for IT-0341 and 11/20/20 for IT-0342.

I've tried nesting an IF statement "ID@row" into the summary sheet to pull from the source sheet but I cant get the formula to not break.

Leibel S

Add the ID as a criteria to both Collects in the formula:

=MIN(COLLECT({Date3},{ID1},ID@row,{New Sheet - ROW},>MAX(COLLECT({New Sheet - ROW},{Status4},"Green",{ID1},ID@row))))

Dale Murphy

@Jeffrey Homme Try:

=LARGE(COLLECT(Date26:Date29, Status26:Status29, <>"green"), 1)

where row 26 is top of range, 29 is bottom of range. This basically shows the most recent date for a row that is not green in status. (The formula returns a date so it needs to be in a date column.)

That should meet this scenario. But I am not sure about the IF, THEN wrinkles you imply with the unless statement.

Cheers,

Dale

Jeffrey Homme

@DMurphy

Thank you for the response. What I'm trying to do is pull the earliest date the Status was in Red/Yellow unless it was followed by a date in Green. I've tried every COLLECT, MIN/MAX, IFS that I can think of but cant seem to get the results shown below. Below are two examples on what I'm trying to do.

Scenario A = 11/4/20

Scenario B = 11/29/20

Leibel S

Jeffery,

Create a helper column called ROW#. It should be a Column formula: =MATCH([Auto-Number]@row,[Auto-Number]:[Auto-Number],0).

Then your formula for pulling the above date would be:

=MIN(COLLECT(Date:Date,[ROW#]:[ROW#],>MAX(COLLECT([ROW#]:[ROW#],[Status]:[Status],"Green"))))

Jeffrey Homme

@Leibel S

That worked, thank you!! One additional question.

For that formula , what statement would I add to have it pull based on the ID in my roll-up sheet?

In the example below the formula you provided would pull 12/2/20 for IT-0341 and 11/20/20 for IT-0342.

I've tried nesting an IF statement "ID@row" into the summary sheet to pull from the source sheet but I cant get the formula to not break.

Leibel S

Add the ID as a criteria to both Collects in the formula:

=MIN(COLLECT({Date3},{ID1},ID@row,{New Sheet - ROW},>MAX(COLLECT({New Sheet - ROW},{Status4},"Green",{ID1},ID@row))))

Jeffrey Homme

@Leibel S

Thank you for both formulas, you have helped me so much!