PDA

View Full Version : Drop-Down List to Display Different Pivot Tables



maverick653
08-29-2019, 08:32 AM
Hello,

Working on an excel project and need help. I am working on a spreadsheet that has a few tabs with the data for 3 different investment portfolios. Each portfolio has a unique name (Port1, Port2, Port3), but it isn't included in the data table itself. The rows are listed by individual investments while the columns display info like region, country, index, and various financial performance metrics. I have created a pivot table for each of these portfolios that are each located on their own tabs (Pivot1, Pivot2, Pivot3). Each pivot table shares the same name as the tab it is located on. I want to create a main page tab that has a drop down list where I can select either Port1, Port2, or Port 3. When I select a portfolio from the drop down list, I would like that portfolio's corresponding pivot table (and any pivot charts) to load below the drop down. If I change to a new portfolio from the drop down, I would like the old pivot table to go away and have the new pivot table appear in its place. Currently, I have no VBA code so far since I am pretty confused on how to begin. If someone could help me with this, that would be greatly appreciated. I am not entirely comfortable with sharing the spreadsheet, since exporting files to third-party sources is against my company's policy. Thank you for your time and consideration.

p45cal
08-29-2019, 03:45 PM
Since there's no file to play with, the attached has 3 sheets with three tables and a sheet called Pivot with a dropdown in cell C18. Change the dropdown and see the pivot table and chart update. Code in the Pivot sheet's code-module.
Things may not be so smooth with your file.

maverick653
08-30-2019, 05:37 AM
Hi,
Thank you tremendously for your help! The code integrates fairly well into the spreadsheet. I appreciate your assistance :)

Jan Karel Pieterse
08-30-2019, 07:29 AM
Wouldn't a simple Table of Contents using the HYPERLINK function suffice? If the sheet name is in B2, this formula links to cell A1 of that sheet:
=HYPERLINK("#'"&B2&"'!A1","Click to go to "&B2)