[Solved] LastCellValue function from Quattro Pro

[rgtr]

Hi

I am moving to OO from Word Perfect Office with Quattro Pro.

It has a great function "LastCellValue(cellref:cellref) e.g. LastCellValue(G6..G1000)

I simply cannot understand how to do this in OO Calc

I have a spreadsheet recording Date, Money In, Money Out, and Balance

I just want to know what the latest balance is ??

I am being driven up the wall by "Index" etc etc.

Richard

[karolus]

If there no »gaps« in the data:

Code: Select all

=INDEX(G6:G10000;COUNT(G6:G10000))

if »last balance« is in the same row as the »last Date« AND the dates are sorted ascending :

Code: Select all

=VLOOKUP(MAX(A6:A10000);A6:G10000;7;1))

[rgtr]

karolus

Thanks
That works a treat .... =INDEX(G6:G10000;COUNT(G6:G10000))

Richard

[Lupp]

LibreOffice 24.8 will come with the new functions XMATCH() and XLOOKUP() both supporting also reverse search directly. (I tested with 24.8.0.3RC.)

[Zizi64]

LastCellValue(G6..G1000)

If you know the boundary of the cell range, then you must know the last cell too: G1000
Just use the formula "=G1000".


Or do you want to reference the value of the last non-empty cell in a cell range?
Just use a manually filled numbering column and the function VLOOKUP() with the parameter MAX('whole cell range of the numbering column') for the searching the last filled-in value in the Balance column.