Dynamic range in Pivot table

W

Wanna Learn

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?
 
D

Debra Dalgleish

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.
 
T

Toppers

..... 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

Dealing with a Pivot Table (when I don't want to) 0
Pivot Table cell values 1
How I can set dynamic pivot table ONLY when I have enough data point 1
Doughnut Chart linked with the slicer in the pivot table 0
Formula to Look up values from a pivot table 1
Pivot Table Counting Text Data 0
Slicers and Calculated Items in Pivot Table 2
Dynmic Range in pivot table 1

Top