Finding Last Entry in a Column

R

RFJ email

Can anyone help. I've got a stock control spreadsheet where a new row is
entered when the stock position changes. Column D has the stock position.

Below the row showing the latest stock position are empty rows for future
use.

At the bottom of the table, below the empty rows, is a cell in Column D
where I want to report the latest stock figure.

Is there a way to do it that isn't upset by those empty cells.

Sample data and outcome would typically look like

Day 1 25
Day 2 33
Day 3 11
<empty> <empty>
<empty> <empty>
<empty> <empty>

Curr Stock 11


Tx
 
R

Ron Rosenfeld

Can anyone help. I've got a stock control spreadsheet where a new row is
entered when the stock position changes. Column D has the stock position.

Below the row showing the latest stock position are empty rows for future
use.

At the bottom of the table, below the empty rows, is a cell in Column D
where I want to report the latest stock figure.

Is there a way to do it that isn't upset by those empty cells.

Sample data and outcome would typically look like

Day 1 25
Day 2 33
Day 3 11
<empty> <empty>
<empty> <empty>
<empty> <empty>

Curr Stock 11


Tx


Assuming your Curr Stock formula is in row 8.

D8: =LOOKUP(2,1/ISNUMBER($D$1:D7),$D$1:D7)

Note the absolute reference to D1 and the relative reference to D7
When you insert rows, the references will adjust appropriately, and should give the correct answer even before you enter data above. In other words, the formula is not dependent on the number of blank lines between your data and the formula.
 
R

RFJ email

Excellent - thanks Ron - worked first time :)


Ron Rosenfeld said:
Assuming your Curr Stock formula is in row 8.

D8: =LOOKUP(2,1/ISNUMBER($D$1:D7),$D$1:D7)

Note the absolute reference to D1 and the relative reference to D7
When you insert rows, the references will adjust appropriately, and should
give the correct answer even before you enter data above. In other words,
the formula is not dependent on the number of blank lines between your
data and the formula.
 

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