GetPivotData

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

geebee - 20 Sep 2007 18:02 GMT

hi,

I have a formula in cell A2 that references D2. like "=D2". D2 happens to
be part of a pivot table (a grand total). how can I make sure that the cel
reference follows or "chases" when the size of the pivot table decreases or
increases. I want to avoind using GetPivotData if possible

thanks in advance,
geebee

Reply to this Message

Jim Thomlinson - 20 Sep 2007 18:06 GMT

Get pivot data is really the only good option. What is your aversion to
getpivotdata? Perhaps we can work around it...

Signature

HTH...

Jim Thomlinson

> hi,
>
[quoted text clipped - 5 lines]
> thanks in advance,
> geebee

Reply to this Message

geebee - 20 Sep 2007 19:08 GMT

well lets say you take a grand total for a value that no longer exists in
the table. then it will be wrongly be based on the grand total for something
else, or you will get the error.

> Get pivot data is really the only good option. What is your aversion to
> getpivotdata? Perhaps we can work around it...
[quoted text clipped - 8 lines]
> > thanks in advance,
> > geebee

Reply to this Message

Jim Thomlinson - 20 Sep 2007 19:40 GMT

The error is easy to get around with an IsError formula something like this

=if(iserror(GetPivotData(...)), 0, GetPivotData(...))

The grand total showing the incorrect amount will be more difficult (no
matter whether you use getpivotdata or not) beause it will reflect the result
based on the filters and aggregations you have in place.

Signature

HTH...

Jim Thomlinson

> well lets say you take a grand total for a value that no longer exists in
> the table. then it will be wrongly be based on the grand total for something
[quoted text clipped - 12 lines]
> > > thanks in advance,
> > > geebee

Reply to this Message