what is the best function for counting rows?

[Jo]

I just want a simple formula to automatically return a result for number of
rows in a sheet (minus the heading). I don't want to have to review the last
row every time it changes or might have changed.

 

[Max]

Assuming your table is in Sheet1, in col A across, headers in row1, data from
row2 down w/o any intervening blank cells

Then in Sheet2,
In A1, copied across: =COUNTA(Sheet1!A:A)-1
will return the required monitoring data row counts for cols A to xxx in
Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000 Files:358 Subscribers:55
xdemechanik

 

[Jo]

Thank you Max - that's perfect. Just for interest's sake, does 'A' refer to
a whole worksheet?

 

[Rick Rothstein \(MVP - VB\)]

And if there are, or can be, intervening blanks...

=MATCH(LOOKUP(2,1/(A1:A65535<>""),A:A),A:A,0)

Rick

 

[Jo]

Thank you Rick. Once a formula gets complicated I lose interest!!

 

[Max]

Thank you Max - that's perfect.
That's good

.. Just for interest's sake, does 'A' refer to a whole worksheet?

If you mean: Sheet1!A:A in the formula,
that term refers to the entire col A in Sheet1
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 16,700, Files: 356, Subscribers: 53
xdemechanik
---

 

[Rick Rothstein \(MVP - VB\)]

But if you can ever have intervening blank cells, then the non-complicated
formula won't work.

Rick

 

[Jo]

Thanks - I'll remember that.

But if you can ever have intervening blank cells, then the non-complicated
formula won't work.

Rick

 

[T. Valko]

Once a formula gets complicated I lose interest!!

You're missing out on all the fun of Excel!

I'm just the opposite. I don't get "interested" unless the formula is
complicated!

 

[Max]

.. I don't get "interested" unless the formula is complicated!

Biff,
There's an OP requesting for a helper col-free solution in this thread:
http://tinyurl.com/649r8g
It might interest you
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,000, Files:358, Subscribers:55
xdemechanik
---

 

[T. Valko]

See my reply in that thread.