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!

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!

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!