We have a scenario where we use multiple sheets for different tracking tasks. These sheets are distinct and large enough (with hundreds of entries) that combining them into a single sheet isn't feasible.
Several people need access to these sheets, and we provide reports for each user to access their assigned task-related data. Hence, users don't need to access the entire sheet; they can just use their Task-Specific Reports. We actually do not want each individual to access the main sheets.
The challenge arises as users need multiple reports, one for each sheet. For instance, a user might have 5 reports assigned to 5 tracking sheets. This seems to be causing confusion among users.
I've tried setting up various dashboards and other methods to access the reports, but so far, there hasn't been a clear solution that every user (and their managers overseeing the assigned tasks) can manage.
So, here's the latest idea I had:
I want to explore creating a single roll-up report that consolidates data from all the main sheets, pulling key fields from each sheet with the same descriptors (such as Description, Start Date, Task).
That part was relatively straightforward.
However, I also want to include a link back to the User-Specific Report for each Task entry.
Unfortunately, this doesn't quite work as intended because:
I can only pull data from a sheet, not a report.
As a result, the "Sheet Name" link only directs users back to the sheet, which isn't what we want.
Is there any way to link to the report or use the report instead of a sheet so that when a user clicks the link, it opens the User Task Report specific to that Task?
Also, the tasks are dynamic, in that new tasks are coming in and getting assigned constantly through a semi-automated form system linked to their respective main sheets.
Any thoughts or ideas would be helpful. We just have a basic account and probably will not be able to upgrade our account to include Control Center or anything else.
