Return values from a sheet
Hello, I have a sheet with the following columns:
Recordable (yes/no)
Date
Description
This sheet has a list of all injuries . . many line items.
In a summary sheet I want to return the "Date" from the most recent line item where "Recordable" = "Yes".
On a separate line on the summary sheet, I want to return the "Description" from the most recent line item where "Recordable" = "Yes".
Any help with the correct formula would be greatly appreciated!!!
Answers
-
Is there a reason you want it on the Summary of this sheet? I would recommend to create a report that you can have filtered for those items unless you absolutely need it on the Summary of the sheet.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
-
@ctsammon I just realized the formula I originally provided you won't work as it will try to find the Max date even for the "No" the below should be correct. Let me know if it works.
=MAX(COLLECT(Date:Date,Recordable:Recordable,"yes")
=Index(COLLECT(Description:Description,Date:Date,[reference your sheet summary cell with the Max date]#,Recordable:Recordable,"yes")
-
Yes, I want to include the date and comment on a dashboard as the most recent recordable injury as a metric.
-
Then use what Hollie has posted above. It should give you what you want.
Jonathan Sanders, CSM
"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
