Excel generate pivot table in new tab

Copper Contributor


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


Here are the steps to create a pivot table based on a dropdown list that selects a sheet name:

  1. Create a dynamic dependent dropdown list in Excel using the Data Validation feature.
  2. In cell G9, use the following formula to get the sheet name from the dropdown list: =INDIRECT("'"&A2&"'!A1").
  3. Create a pivot table by selecting the data range and clicking on the “PivotTable” button in the “Tables” group on the “Insert” tab.
  4. In the “Create PivotTable” dialog box, select the “Use an external data source” option and click on the “Choose Connection” button.
  5. In the “Existing Connections” dialog box, select the Excel file that contains the sheet names.
  6. In the “Create PivotTable” dialog box, select the “PivotTable” option and click on the “OK” button.
  7. In the “PivotTable Fields” pane, drag the fields that you want to include in the pivot table to the “Rows”, “Columns”, and “Values” areas.
  8. 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! 



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?