Software

Ask something
Discussion Locked

Excel-return last non-blank

0 helpful
5 replies

by johnnyextreme 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I need to have cell display the last value in a column on another sheet, but I won't know ahead of time which row that value will be in. Is there a formula that will return the value of the last non-blank cell in a column?

5 replies

by ann777

I don't think that there's a way to do that exactly, but an alternative may be (if that number in the column is the only number that will be in that column alwas), is to put a sum formula at the bootom of that column and pickup the total from thatconsistent cell.

If there's more than one number that will be in the column, then this will not work.

by johnnyextreme

The column contains dates and what I want is the end date. Because the column (B) contains more or less consectutive dates I am able to find the total number of rows that have data using the following formula =(SUM(B:B))/B1. Maybe there is a way touse this formula to reference the column and row that has the end date.

by dklippert

Define a name (Insert>Name> Define. Put a name in the top box and the formula below.):

=OFFSET(Sheet1!$A$2,COUNTA(Sheet1!$A$2:$A$500)-1,0)
There is a very good discussion of this problem at:
http://www.cpearson.com/excel/named.htm#Dynamic
(there are no spaces in the URL)
There is also a sample you can download to practice with.

by johnnyextreme

Excellent! I defined the name EndDate and put the following formula in the 'Refers to' box: =OFFSET('Man Hours'!$B$3,COUNTA('Man Hours'!$B$3:$B$63)-1,0)The worksheet name is Man Hours, the dates were in column B and cells 3 to 63. On the other sheet I just pasted the name =EndDate into a cell and formatted it as a date and now it shows the last date entered.

This discussion is closed to new replies.