How to return a value from the range of cells with the most recent date.
I have a sheet like the one above for each of my 100 employees. I want to be able to run a report on all these 100 sheets such that I can see the most recent date of an employee review and what the expectations for the next period will be. I am trying to use row 6 (highlighted in yellow) as the row to pull the most recent date and expectations for next period. I have read the tutorials on index, match, collect, max etc and been able to get the most recent dates using the max function. But I have not been able to get the words from the expectations column using the any combination of the index match and collect to work. Can someone hlep?
Best Answers
-
@Valerie Harris. Yes, that would be super helpful. Thank you.
-
I figured it out @Jen Lange. This is the formula I used. =INDEX(Celebrations:Celebrations, MATCH(MAX([Date of employee/Manager review]:[Date of employee/Manager review]), [Date of employee/Manager review]:[Date of employee/Manager review], 0)). Where the column celebrations contains the text information I want for the cell, on the most recent date of a manager review column. Works like a charm
Answers
-
Hi @Valerie Harris. It's hard to develop a formula without access to the sheet(s) for reference and testing; however, based on what you've stated and your screenshot, it would seem that you could just use a VLOOKUP to acquire the expectations for the next period.
If you know the employees name, the date of the last review and the sheet that information is stored within, those values should satisfy a VLOOKUP. Only hurdle is that you'll need to move the Expectations column to the right of your date columns or add a reference column to the front of the sheet to satisfy the lookup.
Let me know if you need more instruction to develop this formula.
-Jen
-
I was hoping to use index and Max so I could keep the columns the way they are.
-
Okay. Thanks for the feedback, Valerie. Let me try to rework the formula today and get back to you.
-
Thanks Jen. I can share the sheet with you if that would be helpful. I
-
@Valerie Harris. Yes, that would be super helpful. Thank you.
-
I figured it out @Jen Lange. This is the formula I used. =INDEX(Celebrations:Celebrations, MATCH(MAX([Date of employee/Manager review]:[Date of employee/Manager review]), [Date of employee/Manager review]:[Date of employee/Manager review], 0)). Where the column celebrations contains the text information I want for the cell, on the most recent date of a manager review column. Works like a charm
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!