Dynamic range in Pivot table

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

 

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

 

[Guest]

Are there any empty cells in the range which will "corrupt" the COUNTA count?

 

[Guest]

Toppers

yes in column A there are some scattered empty cells

 

[Guest]

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