INDEX and REFERENCE with criteria


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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!