Forum Discussion
Excel generate pivot table in new tab
Hi,
I have an workbook excel file which the number of sheets are random, but the headers of the sheets will be consistent. i have also a sheet, which will have a dropdown list of all the sheets e.g. in cell A2.
Based on the value of the A2 (sheet selected), G9 should create a pivot table.
Is there any possible way to do this? if so, how?
I'm stuck after creating the dropdown list..
4 Replies
Attached is to illustrate what NikolinoDE says.
- hi, i tried to select other sheet from the dropdown, but the pivot table did not change..
tyraaaa Do you select names?
Here are the steps to create a pivot table based on a dropdown list that selects a sheet name:
- Create a dynamic dependent dropdown list in Excel using the Data Validation feature.
- In cell G9, use the following formula to get the sheet name from the dropdown list: =INDIRECT("'"&A2&"'!A1").
- Create a pivot table by selecting the data range and clicking on the “PivotTable” button in the “Tables” group on the “Insert” tab.
- In the “Create PivotTable” dialog box, select the “Use an external data source” option and click on the “Choose Connection” button.
- In the “Existing Connections” dialog box, select the Excel file that contains the sheet names.
- In the “Create PivotTable” dialog box, select the “PivotTable” option and click on the “OK” button.
- In the “PivotTable Fields” pane, drag the fields that you want to include in the pivot table to the “Rows”, “Columns”, and “Values” areas.
- In the “PivotTable Fields” pane, click on the dropdown arrow next to the field that you want to use as the filter and select the value from the dropdown list.
I hope this helps!
