Formula IFS from other source sheet

Hi good day. I have this formula right now that is returning one value, but it still is missing some part. I need

=IFERROR(INDEX({Weekly Status - Overall Status}, MATCH(MAX(COLLECT({Weekly Status - Date}, {Weekly Status - Complete}, 1)), {Weekly Status - Date}, 0)), "N/A")

I have an "input sheet" where all the projects are listed by row. Each project has multiple columns, including "Project Name" and "Overall Status" (R, Y, G). This is where the PMs will input the weekly status summary for each project.

Everything is good so far.

Secondly, I created a Metadata sheet for each project. Here, I need a cell called "Overall Status" to return the value for a specific project "X". Essentially, I want to be able to pull the "Overall Status" or any other related information for a specific project from the input sheet. In the formula, I need to reference the project I am looking for in the "input sheet

Below some screenshots

Input sheet

Metadata sheet. Right now, is returning the first value of the project with Date 5/27 and that is checked in the Input sheet

Thanks

Find more posts tagged with

Accepted answers

Paul Newcome

Yes. The helper cate column in the metadata sheet should have a formula in the same row as the current week data you are trying to pull over. This cell will have just the MAX/COLLECT piece of your original formula.

All comments

Paul Newcome

You will need to create a date type helper column to pull in the most recent date using the MAX/COLLECT. Then you would use an INDEX/COLLECT along the lines of

=INDEX(COLLECT({Column To Pull Over}, {Project Name}, @cell = Text2, {Date Column}, @cell = [Helper Date]@row), 1)

Raul Cabrera

@Paul Newcome hi. What do you mean with Date type helper? at this moment i have a Date column (Week date)

Paul Newcome

I mean a date type column with the MAX/COLLECT that you are using in your existing MATCH function.

Raul Cabrera

@Paul Newcome

Sorry I don't if there is some resource (video or documentation) where i can go to visit and understand how to do this. I have stuck many days with this without any advance. I understood that I need to create an additional column (helper date) in the Input sheet or Metadata? the helper date column should have a formula?

Thanks in advance

Paul Newcome

Raul Cabrera

Amazing!!! It works @Paul Newcome. Thanks.

Just for reference, in case others have the same question, below is what I did with Paul's help

I created a formula

=(MAX(COLLECT({Weekly Status - Date}, {Weekly Status - Complete}, 1)))

This formula needs to be in a "Date column" with date properties. This in the Metadata file (destination sheet) in the same row where I wanted that the Project Status return the value (next steps)

2. After that in the column Text "Current project status" I wrote the new formula

=INDEX(COLLECT({Weekly Status - Overall Status}, {Project Name}, @cell = Text2, {Weekly Status - Date}, @cell = Date@row), 1)

And that will be going to look up the Project status by project name into the Input Sheet (last value accord to the date).

Then now every time that you change the Status project into the Input sheet or create a new project with the most recent date you will have the last value in this case for the project named "CAO project A"

I hope this helps. Sorry for my English; I hope it is understandable and can assist others.

Paul Newcome

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