Hi,
I have a number of Pivot Tables that look at the same data, and produce
different views and charts.
If my Data Ranges changes [ ie I add a new column or a number of new rows ]
I have to manually change the range for each pivot table - is there anyway I
can define a range one [ in a cell or cells ] and then point each pivot table
to that cell, so that when I want to change the range I only have to do it
once.

I can't point the PT to a "whole" sheet as I've got merged cells.

Thanks

Hi Dave,

Name your data range, apply that as the source for the pivot tables. When
the data range changes, redefine the range associated with the Name.

HTH
Bob

"Dave Eade" wrote in message
...
Hi,
I have a number of Pivot Tables that look at the same data, and produce
different views and charts.
If my Data Ranges changes [ ie I add a new column or a number of new
rows ]
I have to manually change the range for each pivot table - is there anyway
I
can define a range one [ in a cell or cells ] and then point each pivot
table
to that cell, so that when I want to change the range I only have to do it
once.

I can't point the PT to a "whole" sheet as I've got merged cells.

Thanks

Bob, how do I "name" the data range - do I just have the range as a text
value in a cell?

ie 'Spreadsheet'!$E$18:$I$224

"Bob Alhat" wrote:

Hi Dave,

Name your data range, apply that as the source for the pivot tables. When
the data range changes, redefine the range associated with the Name.

HTH
Bob

"Dave Eade" wrote in message
...
Hi,
I have a number of Pivot Tables that look at the same data, and produce
different views and charts.
If my Data Ranges changes [ ie I add a new column or a number of new
rows ]
I have to manually change the range for each pivot table - is there anyway
I
can define a range one [ in a cell or cells ] and then point each pivot
table
to that cell, so that when I want to change the range I only have to do it
once.

I can't point the PT to a "whole" sheet as I've got merged cells.

Thanks

Using Excel <=2003:
Insert, Name Define

Using Excel 2007:
'Formulas' Tab, Define Name

Names must begin with a letter and cannot contain spaces.

When you have named your range, you will need to put this name into the data
source for the Pivot Table, in place of the range address. I'm currently
using Excel 2007 and can't find how to do this just now... (blush). In Excel
<=2003 invoke the wizard and click the Back button.

Each time you make this change you will need to Refresh the Pivot Table
before the new fields/records can be included.

HTH
Bob






"Dave Eade" wrote in message news
...
Bob, how do I "name" the data range - do I just have the range as a text
value in a cell?

ie 'Spreadsheet'!$E$18:$I$224

"Bob Alhat" wrote:

Hi Dave,

Name your data range, apply that as the source for the pivot tables. When
the data range changes, redefine the range associated with the Name.

HTH
Bob

"Dave Eade" wrote in message
...
Hi,
I have a number of Pivot Tables that look at the same data, and produce
different views and charts.
If my Data Ranges changes [ ie I add a new column or a number of new
rows ]
I have to manually change the range for each pivot table - is there
anyway
I
can define a range one [ in a cell or cells ] and then point each pivot
table
to that cell, so that when I want to change the range I only have to do
it
once.

I can't point the PT to a "whole" sheet as I've got merged cells.

Thanks

Excel 2007:

Select a cell in the pivot table. Select the Pivot Table Options tab, Change
Data Source.

Phew...

"Bob Alhat" wrote in message
...
Using Excel <=2003:
Insert, Name Define

Using Excel 2007:
'Formulas' Tab, Define Name

Names must begin with a letter and cannot contain spaces.

When you have named your range, you will need to put this name into the
data source for the Pivot Table, in place of the range address. I'm
currently using Excel 2007 and can't find how to do this just now...
(blush). In Excel <=2003 invoke the wizard and click the Back button.

Each time you make this change you will need to Refresh the Pivot Table
before the new fields/records can be included.

HTH
Bob






"Dave Eade" wrote in message news
...
Bob, how do I "name" the data range - do I just have the range as a text
value in a cell?

ie 'Spreadsheet'!$E$18:$I$224

"Bob Alhat" wrote:

Hi Dave,

Name your data range, apply that as the source for the pivot tables.
When
the data range changes, redefine the range associated with the Name.

HTH
Bob

"Dave Eade" wrote in message
...
Hi,
I have a number of Pivot Tables that look at the same data, and
produce
different views and charts.
If my Data Ranges changes [ ie I add a new column or a number of new
rows ]
I have to manually change the range for each pivot table - is there
anyway
I
can define a range one [ in a cell or cells ] and then point each
pivot
table
to that cell, so that when I want to change the range I only have to
do it
once.

I can't point the PT to a "whole" sheet as I've got merged cells.

Thanks

Great thanks Bob !!!!

"Bob Alhat" wrote:

Excel 2007:

Select a cell in the pivot table. Select the Pivot Table Options tab, Change
Data Source.

Phew...

"Bob Alhat" wrote in message
...
Using Excel <=2003:
Insert, Name Define

Using Excel 2007:
'Formulas' Tab, Define Name

Names must begin with a letter and cannot contain spaces.

When you have named your range, you will need to put this name into the
data source for the Pivot Table, in place of the range address. I'm
currently using Excel 2007 and can't find how to do this just now...
(blush). In Excel <=2003 invoke the wizard and click the Back button.

Each time you make this change you will need to Refresh the Pivot Table
before the new fields/records can be included.

HTH
Bob






"Dave Eade" wrote in message news
...
Bob, how do I "name" the data range - do I just have the range as a text
value in a cell?

ie 'Spreadsheet'!$E$18:$I$224

"Bob Alhat" wrote:

Hi Dave,

Name your data range, apply that as the source for the pivot tables.
When
the data range changes, redefine the range associated with the Name.

HTH
Bob

"Dave Eade" wrote in message
...
Hi,
I have a number of Pivot Tables that look at the same data, and
produce
different views and charts.
If my Data Ranges changes [ ie I add a new column or a number of new
rows ]
I have to manually change the range for each pivot table - is there
anyway
I
can define a range one [ in a cell or cells ] and then point each
pivot
table
to that cell, so that when I want to change the range I only have to
do it
once.

I can't point the PT to a "whole" sheet as I've got merged cells.

Thanks

Bob,
I've named the range thays fine, however in 2007 the Pivot Table is asking
for a range - how do I put the "name" in the dialogue box?

"Bob Alhat" wrote:

Excel 2007:

Select a cell in the pivot table. Select the Pivot Table Options tab, Change
Data Source.

Phew...

"Bob Alhat" wrote in message
...
Using Excel <=2003:
Insert, Name Define

Using Excel 2007:
'Formulas' Tab, Define Name

Names must begin with a letter and cannot contain spaces.

When you have named your range, you will need to put this name into the
data source for the Pivot Table, in place of the range address. I'm
currently using Excel 2007 and can't find how to do this just now...
(blush). In Excel <=2003 invoke the wizard and click the Back button.

Each time you make this change you will need to Refresh the Pivot Table
before the new fields/records can be included.

HTH
Bob






"Dave Eade" wrote in message news
...
Bob, how do I "name" the data range - do I just have the range as a text
value in a cell?

ie 'Spreadsheet'!$E$18:$I$224

"Bob Alhat" wrote:

Hi Dave,

Name your data range, apply that as the source for the pivot tables.
When
the data range changes, redefine the range associated with the Name.

HTH
Bob

"Dave Eade" wrote in message
...
Hi,
I have a number of Pivot Tables that look at the same data, and
produce
different views and charts.
If my Data Ranges changes [ ie I add a new column or a number of new
rows ]
I have to manually change the range for each pivot table - is there
anyway
I
can define a range one [ in a cell or cells ] and then point each
pivot
table
to that cell, so that when I want to change the range I only have to
do it
once.

I can't point the PT to a "whole" sheet as I've got merged cells.

Thanks