INDEX and REFERENCE with criteria

HueyD

Hi,

A bit of background of my problem, we have 3 sheets, Project, Issue and Risk sheets. I want to run a report based on a Schedule RAG (red and yellow) in Project sheet and list all issues and risks associated to the project, however the Issue and Risk sheets do not have Schedule RAG column for the filter to work.

I have been trying to create Schedule RAG columns in Issue and Risk sheets and either index or reference it back to Project sheet.

The project ID has multiple rows, project and milestones underneath. Criteria is INDEX row is [Project or Milestone], "Project". Below is my current formula but I'm not sure where to put the condition for it to work

=INDEX({Copy of Project Repository Range 1}, MATCH([Project ID]@row, {Copy of Project Repository Range 2}, 1), 14)

Paul Newcome

Try an INDEX/COLLECT similar to this...

=INDEX(COLLECT({Project Sheet RAG}, {Project Sheet Project Title}, [Project Title]@row, {Project Sheet Project or Milestone}, @cell = "Project"), 1)

Bassam Khalil

Hi @HueyD

Hope you are fine, Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

Paul Newcome

I agree. Screenshots would be very beneficial. Especially if you are able to provide screenshots with mock data manually entered that shows exactly what you are trying to accomplish.

HueyD

Mock Data.xlsx

Thank you all, I have uploaded the excel spreadsheet with some data and screenshot attached :) What I'm trying to reference is the Schedule RAG column in Issue Sheet with Project info sheet, only the rows where "Project or Milestone" is Project.

Paul Newcome

Try an INDEX/COLLECT similar to this...

=INDEX(COLLECT({Project Sheet RAG}, {Project Sheet Project Title}, [Project Title]@row, {Project Sheet Project or Milestone}, @cell = "Project"), 1)

HueyD

Thanks @Paul Newcome, when I try this formula it returns 90% correct result, the other 10% was indexing different columns for some reason.

Is there a reason for using project title instead of project ID? I also tried using project ID but got "Invalid Value". My Project ID is a set of formula referencing back to Project Sheet.

Paul Newcome

Project ID looks like it could be either numerical or text values depending on how exactly it was populated on each sheet whereas Project Title will always be text values. You won't get a match if one is a number and the other is a text, so I used Project Title just to be on the safe side with format consistency.

If it is pulling from the wrong column, then you are going to need to adjust your ranges to select ONLY the column referenced in the cross sheet reference.

HueyD

Sorry about the late response, the formula works perfectly now :) Thanks @Paul Newcome

Paul Newcome

Happy to help. 👍️