Pivot Table using dynamic worksheets

[flummoxed]

I have a workbook with several worksheets. Each sheet is a snapshot view of
data from any given day or time, and I want to be able to provide the
capability to view any day's data using the same pivot table. I don't want
to combine worksheet data, as each sheet is its own historical record. Each
sheet has the exact same layout -- headings and all. My pivot table is set
up to use columns A through AD ($A:$AD), and I can manually adjust the sheet
I'm querying against by changing the sheet name in the source range field.
However, I want to use a drop down list of all the worksheets in the workbook
to handle this automatically. I figured out - with the assistance of threads
here - how to get a list of all the worksheets, and I have named that range.
Is it possible to somehow use that named range in the source range field for
the pivot table?

Thanks in advance!

 

[Roger Govier]

Hi

Assuming Sheet 1 is where you have your dropdown list of sheet names, and J1
is the cell with the dropdown.
Insert>Name>Define
Name myData
Refers to =INDIRECT("'"&Sheet1!J1&"'!A:H")

Note the mix of double and single quotes, as shown below with spaces to
emphasize
=INDIRECT( " ' "& Sheet1!J1 & " '! A:H" )

Adjust Sheet1 and j1 to suit your case, remembering if Sheet1 has spaces,
enclose within single quotes 'Sheet 1'

On your PT>right click>PT Wizard>Back>Source =myData>Finish

 

[flummoxed]

Hold your cards, we have a BINGO! Thanks a bunch, Roger!