Creating a new @ function in Lotus 123v9

[fwfarr]

How can I create a Lotus 123 @ function that will return the contents of the last cell (furthest from cell A1) that is not blank in a block of cells? This is the same as the QuattroPro @ function 'lastcellvalue'.

 

[123r5w]

Ok... that sounds like QuatroPro has something better than 123r5w and that just can't be... I might have to make an exception to my belief that "123r5w rules"... anyway if your layout is something like this: Column A contains the @ formula (wait for it), column B thru H contains your data and... this is important... you have no blank cells between data input in column B thru to the column whose data you want to attract... enter the following formula to cell A1: @VLOOKUP(B1,B1..H1,@PURECOUNT(C1..H1))

For Example
A(formula) B C D E F G H
11 9 5 8 12 11
8 11 9 8
15 2 11 2 17 45 6 15

Wuddayathink

 

[fwfarr]

Hi! Thanks for the try. Actually I have found a way to "find the value of the last cell in a range", even with blank cells in the range. It involves using the Create>@Function from the top menu of an open spreadsheet to open the script editor and creat the function. Following is the exact method.

In an open spreadsheet, click ‘Create>@Function’ to open the Script Editor.
Type the name you want to call the new script.
Under ‘Object:’ make sure (Globals) appears.
Under ‘Script:’ make sure the new script name appears.
Delete the ‘Function, etc.’ words in the space below.
Copy all of the text below into that space exactly as shown between the lines below.
____________________________________________
Function LastCell(lastrange) As Variant
Dim LastRangeCells As Variant, numcells As Long

Set LastRangeCells = lastrange.cells
lastcell = 0

NumCells = LastRangeCells.count

While lastcell = 0
lastcell = LastRangeCells(NumCells-1).cellvalue
numcells = numcells-1
If NumCells < 0 Then Goto finish
Wend
finish:
End Function
________________________

Click File>Save>OK.

In an open spreadsheet, type: @lastcell(the range you want) in the cell in which you want the ‘last cell value’ to appear and hit ENTER. That’s it!
Note: You can't use this function from the @Function list. You must type it in.