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

"Wanna Learn" wrote:

Toppers

yes in column A there are some scattered empty cells

"Toppers" wrote:

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

"Wanna Learn" wrote:

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?