Pivot Table Ranges
 

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

Pivot Table Ranges
 

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

Pivot Table Ranges
 

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

Pivot Table Ranges
 

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

Pivot Table Ranges
 

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

Pivot Table Ranges
 

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

Pivot Table Ranges
 

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

Pivot Table Ranges
 

'Select Table or a range' option
Type the name in the Table/Range: box






"Dave Eade" wrote in message
...
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

Pivot Table Ranges
 

Hi Dave

If you are using XL2007, place your cursor in the first cell of your
tableInsert tabTableMy data has header rowOK
There is an option to name the Table.
Click on a cell in the newly formed TableTable optionsPropertiesTable
Name
From the same Options tabSummarize with Pivot Table will take that table as
the Source data.
--
Regards
Roger Govier

"Dave Eade" wrote in message
...
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