Need help with a formula

Adam Qualls

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

Right now this is checking the flag in 2 rows that have the same date in the column Pay Period Ending Date (PPE). I have another column called [Created] that captures the date and time. I would like to include this in the formula so in this case when it finds 2 that are the same, then it will select the one that is the newest or most recent in the [Created] column. To put it another way, I need the max from above and then the max from the subset. Any suggestions? It needs to be integrated into the current formula because of the additional logic on the spreadsheet and the way data is being collected so I cannot just tell it to look at created date alone.

Thank you!

Ramzi K

@Adam Qualls

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

Ramzi K

@Adam Qualls

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

Adam Qualls

@Ramzi K

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.

Ramzi K

@Adam Qualls

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

Adam Qualls

@Ramzi K

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?

Ramzi K

@Adam Qualls

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

Adam Qualls

@Ramzi K

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

=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))

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:

1. Compare a list of all rows that have the same department value
2. Within the department list, compare all PPE dates
3. Find the most current PPE date and then flag that row as "Most recent update"
4. 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
5. 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"

Ramzi K

@Adam Qualls

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

Adam Qualls

@Ramzi K

Thank you. I used your two formulas and then in my original checkbox column, I just said if both of your values are true, then check my original column and it seems to be doing the trick.