display question

[Guest]

here's an example:

Column A-N contains month end header (jan -dec).

row 1 has values for each month.

Is there a formula in Column O that would show the last value entered in columns A-N?

 

[NateO]

Your could put the following function in a normal module in your workbook:

Function LASTINROW(rngInput As Range) As Variant
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Rows(1).Columns("a:n")
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINROW = WorkRange(i).Value
Exit Function
End If
Next i
End Function

Then enter the following formula in o1:

=LASTINROW(A1:N1)

Hope that helps. Cheers,

Nate
This message was edited by nateo on 2002-03-11 11:07

 

[Aladin Akyurek]

On 2002-03-11 09:27, Anonymous wrote:
here's an example:

Column A-N contains month end header (jan -dec).

row 1 has values for each month.

Is there a formula in Column O that would show the last value entered in columns A-N?

If the values are numbers or dates, you can use:

=INDEX(A5:N5,MATCH(9.99999999999999E+307,A5:N5))