Pivot Table using dynamic worksheets
 

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!

Pivot Table using dynamic worksheets
 

Hi

Assuming Sheet 1 is where you have your dropdown list of sheet names, and J1
is the cell with the dropdown.
InsertNameDefine
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 PTright clickPT WizardBackSource =myDataFinish
--
Regards
Roger Govier

"flummoxed" wrote in message
...
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!

Pivot Table using dynamic worksheets
 

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

"Roger Govier" wrote:

Hi

Assuming Sheet 1 is where you have your dropdown list of sheet names, and J1
is the cell with the dropdown.
InsertNameDefine
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 PTright clickPT WizardBackSource =myDataFinish
--
Regards
Roger Govier

"flummoxed" wrote in message
...
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!