Pivot table

[sant527]

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

 

[Roger Govier]

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.

 

[sant527]

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.

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.

 

[Roger Govier]

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.

 

[sant527]

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.

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.

 

[Roger Govier]

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