Need help getting the 'most recent' entry in a column (in the simplest format)

I have several sheets that take a doctoral student to unpack to figure out why they work and I'm hoping that Smartsheet has simplified so that I can pare back some of our formulas. One of the most common needs is for us to feed a 'summary' area at the top of the sheet with the most recent entry at the bottom. For example, the DATE of REPORT and the NUMBER fields in line two and three we want to automatically populate when the next one if complete.

In some of my more complicated formulas, we look for the first time 'date of report' is not blank, and then try to report the number. There has to be an easier way.

MAX is working for Date of Report because it's always the latest...It will automatically go down to the end of weeks and find the most recent as it's added.

=MAX([Date of Report]5:[Date of Report]57)

The 'Number' column is not working with MAX because the number could fluctuate. INDEX is working, BUT it won't go to line 57 and takes manual care and feeding. Here is what I have now that it working, but it is blank if I change the number to anything above '8'.

=INDEX(Number5:Number57, 8)

Anyone have a streamlined idea?

Find more posts tagged with

Formulas

DataTable

Accepted answers

Annaleah Morrow

That is so helpful. Thank you! It worked like a charm except for the Last Report Number...I can't get it to show 45 which is the report for the last date of 4/9. Obviously. I can't use 'MIN'. Here is what I see:

All comments

KPH

Hi @Annaleah Morrow

MIN and MAX are the easiest ways to find the dates you need.

You can use INDEX combined with MATCH and the MIN or MAX to return the number associated with the report. Assuming that is what you want. Number in row 3 should be 45, right?

If you put the summary details into a sheet summary, rather than at the top of the sheet you can reference the entire column and not be worried about exceeding row 57.

Use this icon on the right:

Add a field, like this:

Enter the formula:

=MIN([Date of Report]:[Date of Report])

This will return the first report date:

Repeat for Most Recent Report using MAX in place of MIN.

Then for First Report Number create a text/Number summary field and use this formula:

=INDEX(Number:Number, MATCH(MIN([Date of Report]:[Date of Report]), [Date of Report]:[Date of Report], 0))

Repeat for Most Recent Report Number.

Now the data is separate from the summary, so you can reference the entire column.

Annaleah Morrow

KPH

Instead of MIN use MAX for the last report. Your INDEX is returning the number in the row where the date matches the date you found using the first formula.

=INDEX(Number:Number, MATCH(MAX([Date of Report]:[Date of Report]), [Date of Report]:[Date of Report], 0))

Annaleah Morrow

Update: That DID work. For some reason there was a lag on the numbers changing, but yay! Thank you very much.

KPH

No problem, always happy to untangle and streamline 😊

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.