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.
If it worked in 3.4 and does no longer in 3.5 can you please file a bug report?
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
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
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.
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.
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, 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!)
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 