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
-
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")
Answers
-
-
Are you able to provide a screenshot with sample data for context?
-
@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.
-
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")
-
@Paul Newcome That worked! Thanks so much!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!