Auto-Filter master sheet for individual projects

JefeJeffay

Bear with me, I am horribly new here.

The Situation:

I have a form that is shared between multiple sheets via its URL. The form is part of a case log sheet which users can submit case numbers to for record. The sheets on which the link resides are part of a project folder that we use as a template (not a SS template - just a folder with the necessary sheets/reports/etc. to be clear). We create new projects by saving the template folder as new and filling in the necessary fields. The case log exists outside of this "template" as a master sheet.

The form URL is used in the template sheet as part of a process that generates a dynamic URL per line and passes data points within each sheet as hidden fields via query string and allows manual input of just the Case ID and/or Escalation ID being submitted. 

e.g. "https://app.smartsheet.com/b/form/ab1cd2ef3?ID.Project=Proj1&Customer=CustomerA"

All case entries are added to a master log sheet which lists each case ID as an individual line item, along with the supporting hidden data passed in the qString.

The Problem:

I have been asked to create a sheet or report that can be included as part of the project template that would filter the case list for that specific project without any additional user configuration. In other words, you copy the template folder and -BANG- it's set up to filter the cases specific to that individual project.

Unlike Excel, there is no FILTER() function that would allow me to easily parse the data based on a common attribute (in this case, Project ID, given that a customer may have more than one project as seen above). My thought was to put the Project ID into a template level sheet via cell linking, VLOOKUP, INDEX/MATCH, or some other option in the Primary column and using that as a reference point to pull data from the master case log sheet, but I couldn't get that to pull more than 1 case value.

Note: The Case log is in the workspace, but not the template folder.

I wasn't able to simply add the case log/form to the template folder because of the dynamic URL which would remain static, thus always pointing to the original form, not the form created in the newly created project.

The idea is to be able to give users a view at the project level of only the cases associated with that project as well as a full report for leadership including all cases for all projects.

I feel like this should be simple but everything I've tried so far has failed. I'm wondering what the community recommends as the best course of action.

Jason Albrecht

Hello @JefeJeffay

Thank you for your detailed explanation, snippet of data and request.

I recognise you have a 'template' folder with project sheets that get saved as new for each new project, with the relative fields filled in as needed.

I'm wondering if a Sheet Summary Report using a/the sheet/s within the template would work?