MrExcel Publishing
Your One Stop for Excel Tips & Solutions

I know this should be easy but.....


Posted by Dwight on April 09, 2001 8:07 AM

Range a1:a100 are numbers I manually enter not more than one day at a time, entering a value into a1 on day 1, a2 on day 2, etc. Some days I skip (for instance a3 might be blank, and in a4 a value is entered on day 4). I would like for cell b1 to return the value which is in the last cell entered in the range a1:a100. Is there a formula (not a macro) I can put in b1 to accomplish this?
Thanks.


Posted by Aladin Akyurek on April 09, 2001 9:49 AM

Array-enter (that is, hit CONTROL+SHIFT+ENTER to enter) in B1:

=INDIRECT(ADDRESS(MAX((A1:A100<>0)*(ISNUMBER(A1:A100))*(ROW(A1:A100))),COLUMN(A1:A100)))

Aladin

Posted by Dwight on April 09, 2001 2:26 PM

Thanks, Aladin! Works great. Would never have gotten there myself.