Return latest value from data range

W

WildWill

I have A1:A5 which typically fill with data in a sequential manner, i.e.
first A1 would contain a value, then A1 & A2 would contain values, then A1,
A2 & A3 would contain values, etc, etc. I want to look into this range of
cells (A1:A5) and always retrun the "latest" value to cell C1.

E.g. If A1 & A2 have values in them and A3:A5 are empty, then the value of
A2 will be returned to C1. If A1, A2 & A3 have values in them then A3's while
A4 & A5 are empty, then A3's value will be placed in C1, etc, etc.

Thanks!
 
D

David Biddulph

=IF(A5<>"",A5,IF(A4<>"",A4,IF(A3<>"",A3,IF(A2<>"",A2,IF(A1<>"",A1,"")))))
 
J

Jarek Kujawa

numbers:
=OFFSET(A1,MAX(IF(ISNUMBER(A1:A5),ROW(A1:A5)))-1,)

strings:
=OFFSET(A1,MAX(IF(LEN(A1:A5),ROW(A1:A5)))-1,)

both formulae array-entered i.e. with CTRL+SHIFT+ENTER
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top