Need Help with Formula, want all project summary sheet to record most recent step completed

Hi I have a summary sheet that contains all projects that my various PM are working on, the project summary sheet looks like this:

Each project then has its own sheet for the PMs to keep track of what steps have been completed for the project, looks like this:

On the summary sheet in the "most recent step completed" column I want the row to look to its related individual project sheet and pull the step name from the first column for the step that was most recently completed based on the date entered in the date complete column.

Is this possible?

Find more posts tagged with

Sheets

Formulas

Project Management

Accepted answers

Ryan Sides

@CDS

Build 2 cross sheet references ({Date} and {Step} for each project sheet)

You're formula would look like:

=INDEX(COLLECT({Step}, {Date}, MAX({Date})), 1)

You would need to build a formula like this (and the related cross sheet references) for each project sheet so you'll need to name them something different for each sheet. (which is why I asked how many you have)

All comments

L_123

Add a modified column (named modified), then do an index match on it

=index([Phase 1]:[Phase 1],match(max(modified:modified),modified:modified,0))

Ryan Sides

Hi CDS: It's possible!

How many project sheets do you have?

There are a few ways to make this happen:

1) Create cross sheet references (video on how to do that) from your summary sheet to index/collect the max date and bring back the Step.

2) Create helper column on each project sheet to determine the step number of the latest date. Then create a cell link directly to that cell on each of your sheets.

3) Use a premium app called Control Center to manage your projects and it would do this linking for you.

Which are you leaning towards?

CDS

modified column won't work cause that updates anytime they update a cell in the row, I only want to pull the step in based on the date they mark it as completed

L_123

@CDS You can use the new automation *record a date* based off a cell change to put the date in, but it might not be correct if multiple are updated in the same day. If you want, you can use a join(collect()) to pull in multiple values if the most recent were updated on the same day.

CDS

Ryan,

I am comfortable with cross reference sheets but I'm not sure what combination of formulas to use to pull this data

Ryan Sides

@CDS

Build 2 cross sheet references ({Date} and {Step} for each project sheet)

You're formula would look like:

=INDEX(COLLECT({Step}, {Date}, MAX({Date})), 1)

CDS

Thank you Ryan!

Ryan Sides

Happy to help! 😀

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