Data Source for Pivot table expands and contracts

[Guest]

The Data that I use for my pivot table keep changing each week. Weekly, I
copy and paste data from another excel spreadsheet into the workbook that
contains the pivot table. Each week I go in and rename the data range before
I refresh the data. Is there a way that I can perform the data refresh on the
new data without worrying whether this week the data contains 25 rows or 50
rows?

 

[Ron Coderre]

See Debra Dalgleish's coverage of Dynamic Range Names:
http://www.contextures.com/xlNames01.html#Dynamic

They automatically expand/contract to accommodate the data.

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

Ron,

Although Debra's article is insightful, it won't solve my problem. I
actually need to overwrites the previous weeks data with the new data which
could contain more or less rows that the prior week. the columns will always
be the same. This data serves as the source for the pivot tables.

Thanks,

Reggie

 

[Guest]

You have to put a formula like the following one. From Menu Define _ Insert _
Name add the formula. P.S. 'Datas O & C' is the name of your data sheet.

=OFFSET('Datas O & C'!$A$1,0,0,COUNTA('Datas O & C'!$A:$A),COUNTA('Datas O &
C'!$1:$1))

 

[NormDeStorm]

You have to put a formula like the one below in your data sheet and you give a name to the formula. To do so you go to Menu Insert _ Name_Define. Add the formula .

=OFFSET('Datas O & C'!$A$1,0,0,COUNTA('Datas O & C'!$A:$A),COUNTA('Datas O &
C'!$1:$1)). Ounce this is done you go to your pivot table and replace the range with the name you give to the formula created.

P.S. 'Datas O & C' is the name of your data sheet.