Dynmic Range in pivot table

[Jay]

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(Data!$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(Data!$2:$2)).

Unfortunately this range cover one extra row!!

Please, let me know how not to include one extra blank row.

Thanks

Jae

 

[Barb Reinhardt]

Did you try this?

=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A)-1,COUNTA(Data!$2:$2)).