Link a report to a report

JRubke

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.

Scott Orsey

Hi @JRubke, It sounds like you have quite the system!

I have an option for you to consider: You could simply add a column to each of those task tracking sheets where you put the url for the report that you would want to open. Be consistent in it's name (like "Report Link") so when you pull it into your main report you can use that column instead of the sheet name column. Does that make sense?

Be well!

JRubke

Thanks for the reply Scott.

So, I thought about this, which is why I put the line about these being dynamic. Each task could get 10-20 new tasks added a day spread out among 10 people fulfilling the tasks.

The only way I could think of doing what you are saying is to create an automation or formula that writes a link to their specific report URL for that task.

I actually do not think it could be done using a formula as each report has its own URL for the specific user.

It might be feasible in an Automation by creating separate automations for each user in order to put the individuals report URL in the Change Cell Value box for each users' report. I have not tried this, but it might work.

Here in lies the trouble with this - Management of the System:

When a user leaves or is reassigned and someone else takes their place the Task Report URL could also change creating a bunch of dead links that need to be fixed. I do try to just change the name of the user for the existing tasks and modify the Filter for the new user's name, so the URL will sometimes stay the same.

Of course, we already have to do this for the Task assignment automation when someone leaves, so I guess one more box is not that hard.

It would be cleaner if I could just create a roll-up report from all the users reports to link back to the specific report that info came from. So far it is looking like this cannot be accomplished based on my research.

I am thinking we just need some better training for users to reduce the confusion. Sometimes we over think how to make it easier for the user. OTOH, seems super simple to me to follow a link, dunno...