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
Click to expand...


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.
Click to expand...
 

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

Similar Threads

IF/AND Statement & searching through select data... 1
(Advanced Excel) Figuring out a formula of continuous progressiontrying to create 19 Formulas into O 5
Repeating a row other than the 1st row as the header row in a table from next page 1
Eliminate 0 values from pivot table 1
Search for empty cells and move cells next to 10
Finding gaps in a Pivot Table 0
Check for values in column A and if found then column B must have value. 3
lookup and return column number 2

Top