Check Box for Current Milestone

Smar Czar

I am looking for help in writing a formula (or using automations) so that the most recent milestone where the status is "INV PKT SENT & PSR XL UPDT" will have the checkbox. As you can see, I can write a formula to check the box but then when the next milestone reaches that same status I now have multiple checkboxes in the "current milestone" column. I was thinking I could change the value using an automation but can't get it right.

Again, looking to only have the checkbox on the most recent milestone where the status is "INV PKT SENT & PSR XL UPDT"

I appreciate this communities continued support.

Thank you

Paul Newcome

Give this a try:

=IF([Milestone #]@row = MAX(COLLECT([Milestone #]:[Milestone #], [Milestone Status]:[Milestone Status], @cell = "INV PKT SENT & PSR XL UPDT")), 1)

Smar Czar

@Paul Newcome you are truly a community champion! Thank you so much!

Paul Newcome

Give this a try:

=IF([Milestone #]@row = MAX(COLLECT([Milestone #]:[Milestone #], [Milestone Status]:[Milestone Status], @cell = "INV PKT SENT & PSR XL UPDT")), 1)

Erin Horiuchi Green

Hello @Smar Czar ,

Can you please try the below as a column formula for "Current Milestone"

=IF(COUNTIF([Milestone Status]:[Milestone Status], "INV PKT SENT & PSR XL UPDT") = [Milestone #]@row , 1, 0)

Smar Czar

@Erin Horiuchi Green , @Paul Newcome - thank you both for such prompt responses. I really appreciate the suggestions and it works!

Paul Newcome

Happy to help. 👍️

Smar Czar

@Paul Newcome - what if I want the checkbox to be the NEXT milestone AFTER the last "INV PKT SENT & PSR XL UPDT" and not the last MS where status = "INV PKT SENT & PSR XL UPDT".

I tried to update your formula and swap out the text in the " " at the end but then it picks the LAST MS where "MS YET TO OCCUR" is the status.

Smar Czar

@Paul Newcome - I think I have it:

=IF([Milestone #]@row = MIN(COLLECT([Milestone #]:[Milestone #], [Milestone Status]:[Milestone Status], @cell = "MS YET TO OCCUR")), 1)

Smar Czar

@Paul Newcome - the plot thickens.

Current Milestone should be checked on the MS row 1 before the next Milestone Status is "MS Yet To Occur". Once the date is reached for the milestone where the status is "MS Yet To Occur" then that becomes the current milestone. So in this image, the current milestone would be #5, until 6/20 - at which point, no matter what the status of #5, #6 becomes the current milestone.

Thanks again for your continued support!

Paul Newcome

Ok. If it is date based, we can still use the MAX/COLLECT (or Erin's COUNTIF method) but look at dates in relation to today.

=IF([Milestone Date]@row = MAX(COLLECT([Milestone Date]:[Milestone Date], @cell <= TODAY())), 1)

Smar Czar

@Paul Newcome - sorry - not following this one….and when I plug in the formula I get an error…..

Paul Newcome

Sorry. Got ahead of myself on that one.

=IF([Milestone Date]@row = MAX(COLLECT([Milestone Date]:[Milestone Date], [Milestone Date]:[Milestone Date], @cell <= TODAY())), 1)

Smar Czar

@Paul Newcome you are truly a community champion! Thank you so much!

Paul Newcome

Happy to help. 👍️