Pivot Table Ranges

[Dave Eade]

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 Alhat]

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]

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

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

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 Alhat]

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

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

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

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 Alhat]

Excel 2007:

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

Phew...

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

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

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

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

 

[Dave Eade]

Great thanks Bob !!!!

Excel 2007:

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

Phew...

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

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

:

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

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

 

[Dave Eade]

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?

Excel 2007:

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

Phew...

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

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

:

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

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 Alhat]

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

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?

Excel 2007:

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

Phew...

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






(e-mail address removed)...
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

:

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

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

 

[Roger Govier]

Hi Dave

If you are using XL2007, place your cursor in the first cell of your
table>Insert tab>Table>My data has header row>OK
There is an option to name the Table.
Click on a cell in the newly formed Table>Table options>Properties>Table
Name
From the same Options tab>Summarize with Pivot Table will take that table as
the Source data.
--
Regards
Roger Govier

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?

Excel 2007:

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

Phew...

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






(e-mail address removed)...
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

:

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

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