Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Source for Pivot table expands and contracts
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Source for Pivot table expands and contracts
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) "Bendinblues" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Source for Pivot table expands and contracts
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 "Ron Coderre" wrote: 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) "Bendinblues" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Source for Pivot table expands and contracts
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)) "Bendinblues" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data Source for Pivot table expands and contracts
"NormDeStorm" wrote: 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. "Bendinblues" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Source Data | Excel Discussion (Misc queries) | |||
Pivot table data source | Excel Discussion (Misc queries) | |||
Pivot Table data source | Excel Worksheet Functions | |||
Pivot Table data source "data source contains no visible tables" | Excel Worksheet Functions | |||
Pivot Table Source Data | New Users to Excel |