Dynamic range in Pivot table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello I have a named ranged and in the refer to is this formula
=OFFSET(Pivot!$A$1,0,0,COUNTA(Pivot!$A:$A),COUNTA(Pivot!$1:$1))
the problem is that it stops at rows 487 so everything ater row 487 is not
included in the pivot table ? what's wrong with the formula?
 
Is there a value in every cell in the used range in column A?
If there are only 487 entries in that column, that's how many rows the
dynamic range will have.
 
..... that's your problem!

Try replacing the first COUNTA with:

=MATCH(LOOKUP(10^10,$A:$A),$A:$A,0)

This will ONLY work if data is numeric.
 

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

Dynmic Range in pivot table 1
automatically update pivot tables when dynamic named range is expa 10
How I can set dynamic pivot table ONLY when I have enough data point 1
Pivot Tables - Count Numbers vs. Count 1
Dynamic formula for Pivot Table 6
DYNAMIC RANGE FOR PIVOT TABLE 1
Adding to an existing Pivot Table 7
Pivot table filter list - listing irrelevant subcategorys 1

Back
Top