Dynmic Range in pivot table

J

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
 
B

Barb Reinhardt

Did you try this?

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How I can set dynamic pivot table ONLY when I have enough data point 1
hLookup dynamic range 2
Dynamically Naming a Table of Data 2
Dynamic range in Pivot table 4
Multiple Dynamic Named Ranges across Columns 3
Dynamic Range for multiple columns 7
Dynamic Range (again) 7
DYNAMIC RANGE FOR PIVOT TABLE 1

Top