Simply using a spreadsheet to manage my finances, and I'd like to be able to use my latest balance in formulae. It's always going to be the lowest (position within the sheet) populated cell, in a certain column.
So far, I've got as far as
=CELL("SHEET", CELL("ADDRESS", CONCATENATE("E", 3+MATCH(".+",SheetName.E3:E99999, -1))))
With "SheetName" being whatever the sheet is called, and "E" being the column. It simply returns "#REF!".
The 3 being added to the match is to account for the headers (column names, etc.)
Another variant of the same formula (without "array"):
=SUMPRODUCT(MAX(ROW(E1:E99999)*(E1:E99999<>"")))
(The function SUMPRODUCT() takes an array as a parameter and returns a single value)
An array formula, like next, find the last no blank row, in the column A:
{=MAX(IF(ISBLANK(A2:A10);0;ROW(A2:A10)))}
Ctrl+Shift+Enter to enter the formula, not only Enter.
LibreOffice is made available by volunteers around the globe, backed by a charitable Foundation. Please support our efforts: Your donation helps us to deliver a better product!
Asked: 2012-07-08 06:43:04 +0200
Seen: 282 times
Last updated: Jul 09 '12
Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.
