INDEX and REFERENCE with criteria
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)
Best Answer
-
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)
Answers
-
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.
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
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.
-
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.
-
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)
-
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.
-
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.
-
Sorry about the late response, the formula works perfectly now :) Thanks @Paul Newcome
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!