MrExcel Publishing
Your One Stop for Excel Tips & Solutions

refer to data in the 'last cell' of a column


Posted by martin bell on January 02, 2002 3:10 AM

I am collecting stock data each day where the latest stock price is always added to the next cell down in in the same column.

In another worksheet, I have a cell whose value should equal the latest stock price. How do I construct a formula such as: =the stock pricethat is in the last/bottommost filled cell in column A"

Hope someone can help.


Posted by Aladin Akyurek on January 02, 2002 3:22 AM

Martin --

It's:

=INDEX(Sheet1!A:A,MATCH(9.99999999999999E+307,Sheet1!A:A))

Aladin

==============


Posted by Martin Bell on January 02, 2002 5:13 AM

Thanks Aladin - it works great and I'm very grateful. Only thing is, its created another problem which I can only explain by example:

My stock data is in column A (say, the range A1:A20). In column B, adjacent to each closing price in column B I have a formula (which calulates the average of the previous 5 stock closing prices in column A).

I have pre-pasted the formula in column B (down to B30) and need to do the same sort of formula as in my original question.

Of course the result I get from using the same formula relates to the cell B30 (the incomplete pre-pasted formula).

How can I change this formula to target the cell in column B that is directly next to my last stock price in column A (i.e. B20)?


Posted by Aladin Akyurek on January 02, 2002 5:53 AM

Martin --

Not sure I understand the problem.

I'd think that

=INDEX(Sheet1!B:B,MATCH(9.99999999999999E+307,Sheet1!B:B))

or

=INDEX(Sheet1!B:B,MATCH(9.99999999999999E+307,Sheet1!A:A)) [ which gets you the average that is in the same row as the latest stock price ]

should give the last computed average in B. I guess that value includes the latest stock price in A.

If this is not what you meant to have, please post the formula that you se in B.

Aladin

========