Pivot table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sant527@gmail.com - 27 Dec 2007 07:24 GMT

I have sheet "Sheet1" with a pivot table.I have defined a global
range name called "members". In pivot table range I used "members"

later I have copied this sheet. Its name became "sheet1(2)".
I have changed the data in the "members" range name. The range to
which the pivot table refers is of sheet1(2). The name "members"
became local name.

When I update the pivot table, both Sheet1 and Sheet1(2) pivot tables
show the same thing.

Later I went to sheet1 and updated the pivot table there, then
sheet1(2) pivot table also changed.

I want the pivot table to work only for that sheets data

Reply to this Message

Roger Govier - 27 Dec 2007 09:07 GMT

Hi
Why not make unique names for the data range, then there can be no
confusion.

On Sheet1, name your range members1
On sheet1(2), name your range members2

Using the PT wizard, change the source on each table to correspond with the
data range you want.

Signature

Regards
Roger Govier

> I have sheet "Sheet1" with a pivot table.I have defined a global
> range name called "members". In pivot table range I used "members"
[quoted text clipped - 11 lines]
>
> I want the pivot table to work only for that sheets data

Reply to this Message

sant527@gmail.com - 27 Dec 2007 10:14 GMT

On Dec 27, 2:07 pm, "Roger Govier"
<roger@technology4unospamdotcodotuk> wrote:

> Hi
> Why not make unique names for the data range, then there can be no
[quoted text clipped - 29 lines]
>
> > I want the pivot table to work only for that sheets data

I can do that but I have to every time go to wizard and change the
name. And also I have to define a different name for the range.
Because this sheet I will be using many times so I want the pivot
table to change as per data on each sheet. But if one sheet I refresh
all others also show the same.

If I select the range independetly on each sheet then it works fine.

Reply to this Message

Roger Govier - 28 Dec 2007 00:27 GMT

Then when you name your data range members, set the refers to dialogue to be
=!$A$1:$G$20
or whatever your data range is precede by an exclamation mark.

Each sheet will pick up the range you choose, relative to the sheet in
question.

Signature

Regards
Roger Govier

> On Dec 27, 2:07 pm, "Roger Govier"
> <roger@technology4unospamdotcodotuk> wrote:
[quoted text clipped - 40 lines]
>
> If I select the range independetly on each sheet then it works fine.

Reply to this Message

sant527@gmail.com - 28 Dec 2007 04:43 GMT

On Dec 28, 5:27 am, "Roger Govier"
<roger@technology4unospamdotcodotuk> wrote:

> Then when you name your data range members, set the refers to dialogue to be
> =!$A$1:$G$20
[quoted text clipped - 55 lines]
>
> > If I select the range independetly on each sheet then it works fine.

I have tried that.

In my template sheet I have changed the referes to (in define name) to
as you said removing the sheet name and starting from !

But when I copied the sheet and I refresh the pivot table then again
the same thing happens. in other sheet also my pivot table changes.

Reply to this Message

Roger Govier - 28 Dec 2007 13:19 GMT

Hi

I have to go out right now, but if you want to mail the workbook direct to
me, I will take a look later.

to mail direct send to
roger at technology4u dot co dot uk
Do the obvious with dot and at

Signature

Regards
Roger Govier

> On Dec 28, 5:27 am, "Roger Govier"
> <roger@technology4unospamdotcodotuk> wrote:
[quoted text clipped - 68 lines]
> But when I copied the sheet and I refresh the pivot table then again
> the same thing happens. in other sheet also my pivot table changes.

Reply to this Message