Pivot Table Ranges

  • Thread starter Thread starter Dave Eade
  • Start date Start date
D

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
 
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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Excel 2007:

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

Phew...

Bob Alhat said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Great thanks Bob !!!!

Bob Alhat said:
Excel 2007:

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

Phew...

Bob Alhat said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
Excel 2007:

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

Phew...

Bob Alhat said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
'Select Table or a range' option
Type the name in the Table/Range: box






Dave Eade said:
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 said:
Excel 2007:

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

Phew...

Bob Alhat said:
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
Click to expand...
Click to expand...
Click to expand...
 
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

Dave Eade said:
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 said:
Excel 2007:

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

Phew...

Bob Alhat said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top