COUNTA gives you a count of how many cells are non-blank in that row. 65536 is the maximum number of rows that OOcalc can handle, so you're counting everything in that row, from the start to the end.

INDEX first defines a region, then returns the value at a given spot.

So if you have ten items, you run COUNTA on your column and it which returns 10. Then INDEX is defined as the same column, and returns the item at position 10. (which should be the value you're looking for.

One caveat: If there are any blank rows in your column, you have to compensate for them. If you have one blank row at the top, for example, your new formula would be:
=INDEX(A1:A65536,COUNTA(A1:A65536)+1) posted by chrisamiller at 5:01 PM on July 9, 2009

Thanks Chrisamiller (the explanation was very much appreciated!) - I still get the 508 error which, apparently means a 'missing bracket'. I can't see how it thinks there is a missing bracket from this function! posted by tomble at 5:48 PM on July 9, 2009

Ah, got it (after some tinkering, guesswork and luck!)

=INDEX(A1:A65536,COUNTA(A1:A65536))

Should be

=INDEX(A1:A65536;COUNTA(A1:A65536))

It seems open office doesn't like a comma between the functions!

Thans again! posted by tomble at 5:55 PM on July 9, 2009

« Older I'm trying to track down a boo... | Help me identify this fantasy ... Newer »

=INDEX(A1:A65536,COUNTA(A1:A65536))

Explanation:

COUNTA gives you a count of how many cells are non-blank in that row. 65536 is the maximum number of rows that OOcalc can handle, so you're counting everything in that row, from the start to the end.

INDEX first defines a region, then returns the value at a given spot.

So if you have ten items, you run COUNTA on your column and it which returns 10. Then INDEX is defined as the same column, and returns the item at position 10. (which should be the value you're looking for.

One caveat: If there are any blank rows in your column, you have to compensate for them. If you have one blank row at the top, for example, your new formula would be:

=INDEX(A1:A65536,COUNTA(A1:A65536)+1)

posted by chrisamiller at 5:01 PM on July 9, 2009