Ask Your Question
0

locating the final cell with data in a column [closed]

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

betwixt gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-18 15:40:24.544297

Comments

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

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

i cheated a little: [formula in cell B2 and whole B column] =IF(NOT(ISBLANK(A2)),A2,B1)

so: if column A has a value in it then use it, otherwise use the last value above in B column, calculated with the same formulae

Annesley Newholm gravatar imageAnnesley Newholm ( 2016-07-07 08:59:49 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2012-05-03 20:55:33 +0200

mahfiaz gravatar image

If you have no empty rows in your data (or your previous solution wouldn't have worked) you could use COUNT() and OFFSET().

I just tried 3.5.2 and Excel 2007 and both gave me #N/A for the case you were after. So I don't think this is a bug.

edit flag offensive delete link more

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 gravatar imagebetwixt ( 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 gravatar imagemahfiaz ( 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 gravatar imagebetwixt ( 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 gravatar imagemahfiaz ( 2012-05-05 20:51:49 +0200 )edit
1

answered 2012-10-09 03:52:15 +0200

Jamie Deith gravatar image

updated 2012-10-09 04:51:57 +0200

Enter the following as an array formula to give the row offset of the last non-empty cell in the column:

=MAX(NOT(ISBLANK(E6:E10000))*ROW(E6:E10000))-ROW(E6)+1

(Use Ctrl-shift-enter after inputting the formula, and LibreOffice will enclose it in {braces} to indicate that it interprets the results as an array formula.)

So, if you put that formula into, say, cell F5, then you can use that value in a subsequent formula, e.g. for the sum of the range:

=SUM(OFFSET(E6, 0, F5, 1))

You can even use a range name to build a shortcut using the Ranges dialog (Ctrl-F3). For example, you could define E_RANGE as OFFSET($E$6, 0, $F$5, 1). Then the sum formula above simplifies to SUM(E_RANGE). If you want to use E_RANGE on other sheets then be sure to define E_RANGE with the sheet name, e.g. OFFSET($MySheet.$E$6, 0, $MySheet.$F$5, 1).

Hope this helps somebody..

Jamie

edit flag offensive delete link more

Question Tools

Stats

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

Seen: 13,567 times

Last updated: Oct 09 '12