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
Check out the Formula Handbook template!