Join 3,553 readers in helping fund MetaFilter (Hide)


How do I select the last number in a column in Open Office Calc?
July 9, 2009 4:41 PM   Subscribe

How do I select the last number in a column in Open Office Calc?

I have a column of numbers which I add to daily. I would like the bottom, most recent number to be automatically shown in a cell on a different tab.

So, on tab 2 I have column A filled with numbers, with today's number being the very bottom number.

On tab 1 I would have a field which would show that latest number.

The versions of this that I found online, such as

=INDEX(A:A,MATCH(1E+30,A:A))

give me a 508 error. What am I doing wrong? I have very little understanding of formulas like this as I've never really dabbled with them before.
posted by tomble to Computers & Internet (3 answers total) 1 user marked this as a favorite
 
Try this:
=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


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 »
This thread is closed to new comments.