Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynmic Range in pivot table
Thanks in advance.
My pivot table needs to be refreshed every time when new data come. I set up a dynamic data source using OFFSET as follow: =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Da ta!$1:$1)) It works fine at this point. I want to insert one extra row in row number 1 and range definition get changed as follows. =OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),COUNTA(Da ta!$2:$2)). Unfortunately this range cover one extra row!! Please, let me know how not to include one extra blank row. Thanks Jae |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynmic Range in pivot table
Did you try this?
=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A)-1,COUNTA(Data!$2:$2)). -- HTH, Barb Reinhardt "Jay" wrote: Thanks in advance. My pivot table needs to be refreshed every time when new data come. I set up a dynamic data source using OFFSET as follow: =OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Da ta!$1:$1)) It works fine at this point. I want to insert one extra row in row number 1 and range definition get changed as follows. =OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),COUNTA(Da ta!$2:$2)). Unfortunately this range cover one extra row!! Please, let me know how not to include one extra blank row. Thanks Jae |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table as Source Range | Excel Discussion (Misc queries) | |||
Pivot Table Range | Excel Worksheet Functions | |||
Pivot Table Date Range | Excel Discussion (Misc queries) | |||
Multiple Range Pivot Table | Excel Worksheet Functions | |||
Setting Range in Pivot Table | Charts and Charting in Excel |