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

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