locating the final cell with data in a column

asked 2012-05-03 16:14:50 +0200

betwixt
3 ● 1 ● 1 ● 2

I have a several sheets of data and one summary sheet of totals extracted from them. New rows are added daily as data is added and I need the summary to show the values from some columns in the most recently added row only (cells in the highest numbered row). Previously I used =VLOOKUP("",sheet_name.E6:E1000,1) which returned the last value in column E of "sheet name" but it no longer works in the last release of Calc. E1000 was chosen because it is numbered higher than any cell containing data. Is there an alternative function I can use to do the same as before? One that returns the address of the highest used cell in any row or column.

delete close flag offensive retag edit

Comments

If it worked in 3.4 and does no longer in 3.5 can you please file a bug report?

moggi ( 2012-05-03 18:44:36 +0200 )edit

Comments

I've been using it for a while without any problem but I'm sure it wasn't the proper way to do it. COUNT tells me how many cells in the column contain data but I'm not sure how to turn that into a formula. It's the value at the bottom of an expanding list I need to read into the summary.

betwixt ( 2012-05-04 00:28:52 +0200 )edit

So let's say column A holds such data row.

If the column holds only numerical data you are off with =OFFSET(A1;COUNT(A1:A1000)-1;0;1;1)

But if your cell is allowed to contain text as well, you need to use =OFFSET(A1;COUNTIF(A1:A1000;"<>''")-1;0;1;1)

mahfiaz ( 2012-05-04 20:43:51 +0200 )edit

mahfiaz, I am indebted to you! The data is numerical and the first solution works perfectly. . It gets quite complicated when the sheet name has to be included twice as well. I would have thought this was such a common operation that a built-in function would be provided. (a hint to developers!)

betwixt ( 2012-05-05 00:19:49 +0200 )edit

You are welcome. You could click on the gray tick on left, so I get 10 karma points and can vote on other's answers :)

Adding a sheet name to this equation is not that bad. Actually most of the equations I use in Calc are long enough to be close to unreadable :(

mahfiaz ( 2012-05-05 20:51:49 +0200 )edit

locating the final cell with data in a column

Comments

2 Answers

Comments

Resources

Donate

Question tools

Stats

Related questions