Excel Forum

Navigate to Selected Pivot Table Site When using a Hyperlink

Paul Benjamin

Member

Members

Forum Posts: 91

Member Since:
July 11, 2017

Offline

March 17, 2022 - 2:15 am

I want to get to a point where I can select a site in PivotTable1 via the Hyperlink and see the relevant sites data on the 'Sites' tab

I have added a Hyperlink that takes it to the sites tab but don't know how to link it it to the relevant data in PivotTable6 on the Pivot Table tab. I think I need to remove the Sites slicer and replace with something else but have come to the end of my knowledge/skills

Any help would be greatly appreciated

Thanks

Paul

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

March 17, 2022 - 4:51 pm

Because the pivot tables have the same source of data, a slicer can control both pivot tables, just add a Site slicer in Report sheet, right click the slicer-Report Connections and check the pivot tables that can be controlled by this slicer. You can copy the slicer in multiple sheets, when you use one of them it will filter all pivots connected to that slicer.

You don't need a hyperlink to a specific site, just a link to A1 cell, because the table will already be filtered via slicer.

Paul Benjamin

Member

Members

Forum Posts: 91

Member Since:
July 11, 2017

Offline

March 22, 2022 - 11:17 pm

Hi Catalin

Thanks for your reply, the issue I have is that the slicer on the 'Sites' tab shows all 244 sites not just the 30 sites showing in PivotTable1 'GP% Variances greater than -10% to Last Week', I have ticked the 'Hide Items with no data' box in slicer settings but that does not fix the issue.

Also, the sites in PivotTable1 change each week as new data is added so I need to either have a way that the slicer updates weekly to only show the sites in PivotTable1 or find another way to show the individual site data via a hyperlink, VBA or a macro?

Hope the above is clear, and any help would be greatly appreciated

Thanks

Paul

Catalin Bombea

Iasi, Romania

Member

Forum Posts: 1944

Member Since:
November 8, 2013

Offline

March 23, 2022 - 5:54 pm

The slicer in sites controls PivotTable1 and PivotTable6, from Report and Pivot Table sheets. They both have the same data source - Sites & Groups table.

Just add a new column in Sites & Groups table named GPVar<=10% with a simple formula:

=[@[GP% Variance]]<=0.1

Then you can add a slicer for this column, will have 2 values only: True and False, the slicer should control both pivots, it should allow you to filter out what you don't want to see.