Need help with a formula

Sometimes a list of requirements is best. Thanks for that!

Here you go.

Note: A combined formula does not appear to be an option because of nesting limitations in Smartsheet. You will need to change the column names to match our sheet. The formulas below match my sample sheet.

Formula for Most Recent PPE Date:

=IF([PPE Date]@row = MAX(COLLECT([PPE Date]:[PPE Date], Department:Department, Department@row)), true)

Formula for Most Recent Date:

=IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], Department:Department, Department@row, [Most Recent PPE Date]:[Most Recent PPE Date], @cell = true)), true)

I hope this does the trick for you.

Cheers,

Ramzi

All comments

Adam, can you please include a screen shot or share your sheet and provide some more detail. It would be easier to explain it with some visuals.

Please tag me with @ so I can take a look.

Cheers,

Ramzi

This is a sample of the smartsheet. I want the formula to only check the circled one and not the crossed out one. The other departments are fine because they only have a single entry with the same PPE date. Since PPE is a date only field, it isn't properly evaluating which one is the max. If I let it continue to use PPE but also include evaluating the created date which includes time, then I can make it select the correct one.

Thanks Adam, that was helpful.

Try this and see if it does the trick -

=IF(AND([Pay Period Ending Date (PPE)]@row = MAX(COLLECT([Pay Period Ending Date (PPE)]:[Pay Period Ending Date (PPE)], Department:Department, =Department@row)), Created@row = MAX(COLLECT(Created:Created, Department:Department, Department@row))), true)

Cheers,

Ramzi

Thank you Ramzi. That worked with the given scenario but I found once I updated the formula, it created a new issue. There is another department where the most recent update is in fact the one with the greatest PPE date, but the created date is not the max date. I need it to only evaluate the created date when there are multiple PPEs that are the same date. Otherwise, it needs to just select the one with the highest PPE date. In the picture below I added your updated formula into the [Most recent 2] column. You can see for the department Training Inservices, it unchecked all of the boxes which will cause reporting errors. The correct box should be the one checked by the old formula. Any idea how to account for this situation with the formula?

Try this:

=IF([Pay Period Ending Date (PPE)]@row = MAX(COLLECT([Pay Period Ending Date (PPE)]:[Pay Period Ending Date (PPE)], Department:Department, =Department@row)), true, IF(AND([Pay Period Ending Date (PPE)]@row = MAX(COLLECT([Pay Period Ending Date (PPE)]:[Pay Period Ending Date (PPE)], Department:Department, =Department@row)), Created@row = MAX(COLLECT(Created:Created, Department:Department, Department@row))), true), false)

Convoluted, but basically you are checking to see if the PPE date is the highest in that range. If it is then go ahead and flag it, otherwise do some more checking.

If this doesn't work we have two options. We either keep at it with screen shots or you can share your sheet with me (ramzi@cedartreeconstulting.com) and we can go from there.

Cheers,

Ramzi

I had to change some parentheses to make it not give an error.

Unfortunately, this undid the work from the first part because now it flags multiple checkboxes when the PPE date is the same.

What I need it to do:

Compare a list of all rows that have the same department value
Within the department list, compare all PPE dates
Find the most current PPE date and then flag that row as "Most recent update"
If there are multiple rows with the same dept and same PPE date, then I need it to make a new list of those rows to then compare Created date
Now I need it to select the most recent created date (out of the rows that have the same dept and PPE date) and then check mark the box for the most current date.

Here is the updated column on the left with the new formula above that is giving the same results as the original formula which was checking multiple boxes for "business office payment resolution"

Sometimes a list of requirements is best. Thanks for that!

Here you go.

Formula for Most Recent PPE Date:

=IF([PPE Date]@row = MAX(COLLECT([PPE Date]:[PPE Date], Department:Department, Department@row)), true)

Formula for Most Recent Date:

=IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], Department:Department, Department@row, [Most Recent PPE Date]:[Most Recent PPE Date], @cell = true)), true)

I hope this does the trick for you.

Cheers,

Ramzi