Complex Smartsheet Formula Help - Duplicate Entry Searching

Stephensr

I'm working on a sheet that logs employee FTE changes across various grants. Over time, one employee might accumulate several changes across different grants.

The sheet columns are as follows: Employee Name, Grant, Name, Previous FTE, New FTE, Approval Status, and Record Status. If two or more records are referencing the same grant and employee, the most recent record should say "Newest Record" in the Record Status column and the older records should change from "Newest Record" to "Old Record". I will then use a report to filter all records with "Newest Record" so it displays current FTEs for each employee. Here is the formula I'm trying to use, but receiving an unparseable error.

=IF(AND([Approval Status]@row = "Approved", [Record Status]@row <> "Newest Record"), IF([Approval Status]@row = MAX(IF([Approval Status]:[Approval Status], [Employee:Employee] = @cell, [Grant Name:Grant Name] = @cell), "Newest Record", IFERROR(IF([Record Status]@row = "Newest Record", "Old Record"), "Old Record")), [Record Status]@row))

Paul Newcome

Try something like this:

=IF(AND([Approval Status]@row = "Approved", [Date Approved]@row = MAX(COLLECT([Date Approved]:[Date Approved], [Employee Name]:[Employee Name], @cell = [Employee Name]@row, Grant:Grant, @cell = Grant@row))), "Newest Record", "Old Record")

heyjay

Paul Newcome

Are you able to provide a screenshot with sample data for context?

Stephensr

Formula_Example.PNG

@Paul Newcome Here is an example picture. This sheet is the back end of a form for managers to submit FTE allocation changes for their staff. Ideally, when we go to this sheet, the newest record approved would be automatically indicated so we aren't sorting through many records to find the most accurate allocations or updating this column manually.

Paul Newcome

Try something like this:

=IF(AND([Approval Status]@row = "Approved", [Date Approved]@row = MAX(COLLECT([Date Approved]:[Date Approved], [Employee Name]:[Employee Name], @cell = [Employee Name]@row, Grant:Grant, @cell = Grant@row))), "Newest Record", "Old Record")

Stephensr

@Paul Newcome That worked! Thanks so much!

Paul Newcome

Happy to help. 👍️