Autocheck a box

Jaz693

I know these have been asked, and I have a formula working but I have a different issue. I want to auto check a box based on the most recent modified date. Im using the following formula:

=if(Modified@row = MAX(modified:modified),1,0)

The issue is it checks the box correctly, but as soon as I set the formula as a column formula and save the sheet every row's modified date/time stamp updates to the exact same time, so every box gets checked. There are no other formulas on this sheet.

See in the screenshots. Anything I can do to stop that from happening?

Paul Newcome

In that case, ignore the Modified piece and try this instead:

=IF([Week of]@row = MAX(COLLECT([Week of]:[Week of], Summary:Summary, @cell <> "")), 1)

Basically we COLLECT all of the dates where [Summary] is not blank then pull out the MAX date. Then we say that IF the date "@row" is equal to that max date, check the box.

Paul Newcome

It is because you are modifying all of the rows by applying the formula. You are going to have to modify a cell in the row you want marked to update the modified date/time in that row, but even then you may run into the issue of the formula unchecking the box on the other rows being considered a modification which in turn will update the modified date/time, so on and so forth.

Is there a specific reason you need to check off the most recently modified row?

Jaz693

Thank you for the clarification. I have about 60 different projects, and each project must update a weekly summary for their project. I then have a dashboard that looks at all project summaries but only want to display the most recent weekly summary for each project. For example, Project A has a weekly summary sheet of 52 rows (one row for each week), the row starts of with Week of in one column, then another column for the summary this goes on for all projects...

I dont want to display all of the data of the summaries in the dashboard so I was thinking that if I could auto check the row with the most recent data, I could then pull that row into a metrics sheet that feeds the dashboard.

Paul Newcome

Would it be safe to say you want the last row where the Summary column is not blank?

Jaz693

Correct

Paul Newcome

In that case, ignore the Modified piece and try this instead:

=IF([Week of]@row = MAX(COLLECT([Week of]:[Week of], Summary:Summary, @cell <> "")), 1)

Basically we COLLECT all of the dates where [Summary] is not blank then pull out the MAX date. Then we say that IF the date "@row" is equal to that max date, check the box.

Jaz693

This worked beautifully! I didn't think of basing off the summary :-) Thank you so much!

Paul Newcome

Happy to help. 👍️