Hello folks!
I'm working to build a series of reports for different stakeholder groups where the goals are to:
- Gather specific pieces of data (columns) from multiple sheets (which all share the same primary IDs)
- Combine data from each sheet into unique rows for each primary ID
- 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!
