Complex Smartsheet Formula Help - Duplicate Entry Searching

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


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!