Find the last non-blank value in a column

[WildAngus]

Can anyone tell me how to quickly return the LAST non-blank value in a column please?

 

[VoG]

See http://www.xldynamic.com/source/xld.LastValue.html

 

[WildAngus]

Thanks for the reply, but I'm still not getting this...

I have entries in column H starting H8, H9, H10 etc
If H42 is the last value for example, then I want to retun this value at the top of the row.
i.e. Cell H7 (result) would return the value in cell H42.

It sounds like there should be a simple way in excel, but I'm not sure how the formula in the link can do this.

 

[VoG]

For numbers try

=LOOKUP(9.9E+307,H8:H1000)

 

[bobsan42]

=indirect("H" & MAX(MATCH(9E+220,H:H),MATCH(REPT("z",150),H:H)))

this should be independent of the data type in H

oops : it will return an error ... it will work if you have both text and number type of data in H

for text in H: =indirect("H" & MATCH(REPT("z",150),H:H))
for numbers: =indirect("H" & MATCH(9E+220,H:H))

call if you need it to be universal.

 

[WildAngus]

Perfect !!!

Thx

 

[lenze]

Just for fun, I would probably use code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Row < 8 Then Exit Sub
If Target.Column <> 8 Then Exit Sub
If Target.Row = Cells(Rows.Count, "H").End(xlUp).Row Then Cells(7, 8) = Target
End Sub

This will update H7 when ever a new value is added to the column
lenze