Looking for the most recent submission based off date
hello I am looking to build a formula that pulls the "Mid/ EOC Rating" from a reference sheet with the criteria that the ID's match, it is the most recent submission based on Evaluation Date, and that the Evaluation was an EOC not any other options
So far I have gotten to the below formula but it is still reading as invalid column value as it pulls up the current date today
=MAX({Tech Evaluations from Ships Range Date of Eval}, INDEX(COLLECT({Tech Evaluations from Ships Range Rate}, {Tech Evaluations from Ships Range ID number}, [Employee ID]@row), 1), " ")
Reference sheet: could have multiple submissions for each person
Best Answer
-
Hi @Ali Simpson
To pull the most recent "Mid/EOC Rating" for a specific ID based on matching ID, the latest Evaluation Date, and only for "EOC" evaluations, try this formula:
=INDEX(COLLECT({Tech Evaluations from Ships Range Mid/EOC Rating},
{Tech Evaluations from Ships Range ID number}, [Employee ID]@row,
{Tech Evaluations from Ships Range Type of Eval}, "EOC",
{Tech Evaluations from Ships Range Date of Eval}, MAX(COLLECT({Tech Evaluations from Ships Range Date of Eval},
{Tech Evaluations from Ships Range ID number}, [Employee ID]@row,
{Tech Evaluations from Ships Range Type of Eval}, "EOC"))), 1)
Let me know if this works and if you have any further questions.
Answers
-
Hi @Ali Simpson
To pull the most recent "Mid/EOC Rating" for a specific ID based on matching ID, the latest Evaluation Date, and only for "EOC" evaluations, try this formula:
=INDEX(COLLECT({Tech Evaluations from Ships Range Mid/EOC Rating},
{Tech Evaluations from Ships Range ID number}, [Employee ID]@row,
{Tech Evaluations from Ships Range Type of Eval}, "EOC",
{Tech Evaluations from Ships Range Date of Eval}, MAX(COLLECT({Tech Evaluations from Ships Range Date of Eval},
{Tech Evaluations from Ships Range ID number}, [Employee ID]@row,
{Tech Evaluations from Ships Range Type of Eval}, "EOC"))), 1)
Let me know if this works and if you have any further questions. -
You are a Legend! Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 405 Global Discussions
- 216 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!