Reference row if its the newest entry

Hi all,

Im looking for a means to pull an entire row's worth of data from one sheet to another provided that it is the latest entry in that sheet.

I have set up a test sheet to capture weekly status updates. I want to be able to pull and reference the newest entries on a dashboard. But because I cannot automatically move new entries to the top, I am forced to pull the newest one into another sheet.

I have used the '=Max' formula to reference the most recent date in the 'Report Week' column in Sheet 1. Now I need to pull the rest of that rows data into Sheet 2 and was wondering if there is an easy formula to use.

Essentially, if the Report Week cell equals the max value, then reference that row's Project, Schedule, Scope Health etc.

Thank you!

Find more posts tagged with

Accepted answers

Daniel Barber

I was able to figure this out and instead of deleting my post, I wanted to share the solution so that it can be refined and reused by others.

In Sheet 2 I used this formula in each column (in this case it was for the Project Health column):

Where the first "{Weekly Statue Update Range…" is referencing all the columns in Sheet 1, the second points to the Report Week (Monday) column in Sheet 1, and the third highlights all of the headers in row 1 of Sheet 1.

Note: I did have to manually add Headers to Row 1 as a reference point. If these don't match up between sheets 1 and 2 you will have errors.

I'm sure this can be cleaned up a little, but I just wanted to share what I had in case it helps others.

All comments

Daniel Barber

I was able to figure this out and instead of deleting my post, I wanted to share the solution so that it can be refined and reused by others.

In Sheet 2 I used this formula in each column (in this case it was for the Project Health column):

Note: I did have to manually add Headers to Row 1 as a reference point. If these don't match up between sheets 1 and 2 you will have errors.

I'm sure this can be cleaned up a little, but I just wanted to share what I had in case it helps others.

Georgie

Hey @Daniel Barber,

Thank you for posting your solution. I'm glad you got this resolved!

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, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.