Identify Most Recent Record

DPranno

Hello, I am using SmartSheet to log Training Records. Every time a record is submitted, I am recording three fields: Date, Name, Topic.

Based on the date, I am projecting forward the next time training needs to occur. (ie: 3 years after most recent training record). This occurs in a "Renewal Date" Column as a column formula.

The problem I run into is that once the training record is repeated, I now have two (or more) due dates with the same Name and Topic. I'd like to have my "Renewal Date" formula only look at the latest combination of Name + Topic + Date. If it isn't the latest Date, then insert a "blank".

The final intent is to have a single report that groups training records by Topic, and shows only records with a due date, indicating if employees are past their training renewal dates. Everything works so far, except duplicated entries in the report due to being unable to filter out (intelligently) old records.

Is this possible? Thanks!

Paul Newcome

Apologies. I missed the part about the topic requirement.

=IF([Training Record Date]@row = MAX(COLLECT([Training Record Date]:[Training Record Date], [Primary Column]:[Primary Column], @cell = [Primary Column]@row, Topic:Topic, @cell = Topic@row)), DATE(YEAR([Training Record Date]@row) + [Topic Frequency (Years)]@row, MONTH([Training Record Date]@row), DAY([Training Record Date]@row)))

Paul Newcome

Try this:

=IF([Training Record Date]@row = MAX(COLLECT([Training Record Date]:[Training Record Date], [Primary Column]:[Primary Column], @cell = [Primary Column]@row)), DATE(YEAR([Training Record Date]@row) + [Topic Frequency (Years)]@row, MONTH([Training Record Date]@row), DAY([Training Record Date]@row)))

DPranno

Thanks Paul, this nearly works! To be specific, it does work, but it works once per user.

In my full list, you may have things like:

Employee Name Trained in "Fire" 1/1/2025

Employee Name Trained in "Electrical" 2/1/2025

Employee Name Trained in "Secondary Containment" 3/1/2025

Your formula will show that only "Secondary Containment" needs a renewal, because it is the latest date. When in reality all three topics should require a renewal because they are all different. My list has.

I am trying to achieve the "Max" of the combination of Name + Topic. IN Excel, I can make a formula that compares multiple arrays and multiplies them together to find what positions have all criteria met. But those type of formulas aren't working (maybe due to data types) in Smartsheet.

Paul Newcome

Apologies. I missed the part about the topic requirement.

=IF([Training Record Date]@row = MAX(COLLECT([Training Record Date]:[Training Record Date], [Primary Column]:[Primary Column], @cell = [Primary Column]@row, Topic:Topic, @cell = Topic@row)), DATE(YEAR([Training Record Date]@row) + [Topic Frequency (Years)]@row, MONTH([Training Record Date]@row), DAY([Training Record Date]@row)))

DPranno

That works very well Paul!

I have now added "Collect" into my mental toolbox. Thanks a lot for that insight and new function.

Paul Newcome

Happy to help. 👍️