Pivot ranges and INDIRECT

Hey Domenic, do you have any good ideas on using an
INDIRECT function to specify a dynamic PivotTable Range
in the PivotWizard Range box. Been struggling with this
for awhile. I figure its impossible. That PivotWizard
range box just doesn't like a "!". It wants to change it
to ".xls!".

some backround? you add rows to the bottom of a range
which you wish to use as a PT range. If its in the same
sheet, the indirect function will work with a simple
refresh of the table. If its in a different sheet, it
fails.

I'm stumped.

Create a dynamic named range, then use the name in the pivot wizard.
There are instructions here:

http://www.contextures.com/xlPivot01.html

daveydave60 wrote:
> Hey Domenic, do you have any good ideas on using an
> INDIRECT function to specify a dynamic PivotTable Range
> in the PivotWizard Range box. Been struggling with this
> for awhile. I figure its impossible. That PivotWizard
> range box just doesn't like a "!". It wants to change it
> to ".xls!".
>
> some backround? you add rows to the bottom of a range
> which you wish to use as a PT range. If its in the same
> sheet, the indirect function will work with a simple
> refresh of the table. If its in a different sheet, it
> fails.
>
> I'm stumped.
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html