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!
-
@Ali Simpson
Love to hear it! Let us know if you or your team need any Smartsheet help in the future!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!