Index/Collect/Max Date
I need to show the most recent entry to a sheet. Details are:
I have one sheet used as a storm log (manually entered). It contains date/time of new entry, Storm number, date of storm and time of storm.
I have created a metrics sheet to collect the most recent entry. How can I use index, collect, max date to show the most recent entry, storm number, storm date, storm time?
Thank you!
Best Answers
-
I would add a helper Checkbox column in the storm log, in that column I use a column formula like the example below to checkmark the most recent entries.
=IF([Entry Date]@row = MAX(COLLECT([Entry Date]:[Entry Date], [Storm Number]:[Storm Number], [Storm Number]@row)), 1)
Now from you metric sheet you can use the checkmark to find the most recent entry for each storm number.
-
Sorry I misunderstood, I though you had multiple reports for the same storm name and wanted to find the latest one for each storm name.
to just find the latest entry use
=IF([entry date]@row = MAX([entry date]:[entry date]), 1)
or to collect the date into a single string
=IF([entry date]@row = MAX([entry date]:[entry date]), JOIN([Storm Name]@row + "," + [Storm Date]@row + "," + [Storm Time]@row))
Answers
-
I would add a helper Checkbox column in the storm log, in that column I use a column formula like the example below to checkmark the most recent entries.
=IF([Entry Date]@row = MAX(COLLECT([Entry Date]:[Entry Date], [Storm Number]:[Storm Number], [Storm Number]@row)), 1)
Now from you metric sheet you can use the checkmark to find the most recent entry for each storm number.
-
Thank you for your assistance.
I have done this, but all of the boxes are checked.
-
Can you post some screen shots of your sheet and formula.
-
Here are a couple of screenshots.
-
Sorry I misunderstood, I though you had multiple reports for the same storm name and wanted to find the latest one for each storm name.
to just find the latest entry use
=IF([entry date]@row = MAX([entry date]:[entry date]), 1)
or to collect the date into a single string
=IF([entry date]@row = MAX([entry date]:[entry date]), JOIN([Storm Name]@row + "," + [Storm Date]@row + "," + [Storm Time]@row))
-
Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!