Create a dynamic link to "drill down" to a source sheet from a report

Freymish

I know there has to be a simple way to do this but I'm not finding it and I getting into crunch time on this project so, HELP! :)

I have a sheet that has a bunch of projects on it. I am pulling a report to a tracking sheet that folks will use as a starting off point to look at project progress. Whenever I add a new project to the source sheet it automagically populates the tracker. Which is perfect. Now I just need an easy way to get people from the tracker to the main project data sheet. (Ideally I'd like to create a report that I can link to, that presents them to just the data from that single project but, baby steps.)

Any advice would be greatly appreciated.

Genevieve P.

Hi @Freymish

Thank you for clarifying!

Have you included in the Sheet Name column in your Report? If so, then when a user clicks on the link to the sheet from the relevant row in the Report, it will take them to that same row in the source sheet.

There won't be a filter applied, but say the Project was on row 5 of the Report but row 200 in the sheet, it would bring them directly there instead of to the top of the sheet.

Cheers,

Genevieve

Genevieve P.

Hi @Freymish

It sounds like you have one Sheet where you list each Project as a new row, but then you also have one sheet per Project, so you want the link to the new Project Sheet in the main Project List sheet, is that correct?

If so, I would suggest copying/pasting the URL of each individual Project Sheet into a column in your overall list sheet. This way you have a URL that users can click on when looking at the List either through a Report or directly on that master list sheet.

There are some Templates in the Solution Center that have a similar set-up. See this Project Intake set. In this example, the individual Project is linked as a Hyperlink in the Project Name column.

Cheers,

Genevieve

Freymish

I only have one sheet for all projects. I pull relevant information from that sheet into some others for reporting purposes so while the user could just go to the main sheet I would prefer that they be directed to the location in that sheet where that project's data is or maybe go to the main sheet with a filter for that one project. (That last bit is a little scope creep from the initial question.) In production there could be 80 to 100 projects getting tracked so I want to reduce the confusion and manual intervention as much as I can. Part of the goal here is to free the PMs from having to manage the Project Management reporting processes and focus on the projects themselves.

Genevieve P.

Hi @Freymish

Thank you for clarifying!

Have you included in the Sheet Name column in your Report? If so, then when a user clicks on the link to the sheet from the relevant row in the Report, it will take them to that same row in the source sheet.

There won't be a filter applied, but say the Project was on row 5 of the Report but row 200 in the sheet, it would bring them directly there instead of to the top of the sheet.

Cheers,

Genevieve

Freymish

I will try that. I didn't realize it did that. I've been so task oriented on this I haven't taken the time to read up on things like that. It's my "special talent" lol

Genevieve P.

No problem at all! Let me know if this is a good solution for your situation once you've tried it. 🙂

Freymish

That will work fine. Is there a way to change the formatting on that field so it isn't just the same text all the way down? A nice to have of course.

Genevieve P.

@Freymish I'm glad to hear it will work for you!

There currently isn't a way to format that column from a Report, unless you had a formatting rule applied to the entire row in the source Sheet. Formatting on a Sheet pulls through to the Report.

For example, you could apply a light Yellow background to the entire row, then select all the visible columns and turn them back to have a White background. This should have applied the Yellow to the row, including the "Sheet Link" hidden column that's only visible in a Report. Does that make sense?