Last Cell Value

  • Thread starter Thread starter Bruce Roberson
  • Start date Start date
B

Bruce Roberson

I have a column with 20 numbers in it from row 1 through
20, and each month, there is an additional number entered
in the next row of that same column.

In Qpro, I had a formula called "@lastcellvalue" and its
syntax was something like this: @lastcellvalue(Range).
Range is this case would be a1:a50 to accomodate room for
values to be added beyond the 20 rows mentioned above.

So, this month, it will pull the value in Cell A20. Next
month, that same formula would pull the value in cell A21.

So how does one accomplish this equivaluent in Excel?

Thanks,


Bruce Roberson
 
Bruce,

It's a little more roundabout in Excel. If there are no missing values in
the cells:

=OFFSET(A1,COUNTA(A1:A50)-1,0,1,1)

If there are:

=OFFSET(A1,MAX(IF(A1:A50<>"",ROW(A1:A50),0))-1,,1,1)

This one must be array-entered (Ctrl-Shift-Enter.
 
How much easier would our lives be if we had
-----Original Message-----
Bruce,

It's a little more roundabout in Excel. If there are no missing values in
the cells:

=OFFSET(A1,COUNTA(A1:A50)-1,0,1,1)

If there are:

=OFFSET(A1,MAX(IF(A1:A50<>"",ROW(A1:A50),0))-1,,1,1)

This one must be array-entered (Ctrl-Shift-Enter.

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------




.
Click to expand...
 
Biff,

How very true.

I've made an attempt at making our lives easier. Only now you have to be
able to paste a user defined function into a module in the VBE, a task not
familiar to all. But here it is:

Function LastCellValue(DataRange As Range) As Variant
For Each thing In DataRange
If thing > largest Then largest = thing
Next thing
LastCellValue = largest
End Function

In a cell, you call it thusly:

=LASTCELLVALUE(A2:A30)
 
Back
Top