Combining multiple sheet Data in 1 report against common primary ID


Hello folks!

I'm working to build a series of reports for different stakeholder groups where the goals are to:

  1. Gather specific pieces of data (columns) from multiple sheets (which all share the same primary IDs)
  2. Combine data from each sheet into unique rows for each primary ID
  3. Allow the stakeholders to edit the data (and have that data in turn be updated in the reference sheets).

I'm using the reports feature to achieve #1.

My main issue at the moment comes from the fact that I can't seem to make #2 and #3 happen concurrently in a report.

If I use an INDEX/MATCH formula to bring the data into a single sheet, I can make all the data show on 1 unique row for each primary ID (#2), but then I can't have my stakeholders update that data in their report (#3) because what they are seeing in their report is the output of a reference formula, not the actual data point from the original sheet that data is coming from.

And if I just pull the information from the different sheets directly into the report, stakeholders are able to do edits and have it reflect back on original sheets (#3) but each sheet generates 1 row for the same primary IDs (#2) so the data is scattered across my report.

I have a lot of data to combine and I'm already using multiple grouping levels, so grouping by primary ID isn't ideal. I'm hoping there's a way to solve my issue in a more elegant fashion.

Thank you!



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!