how can I reference the first value in a column of numbers when ......

[Joe Miller]

In a formula, how can I reference the first value in a column of numbers when the row number of that first number is not constant [ie] the row number changes regularly from day to day as I insert new rows at the top of the column each day?
[and the row number is always greater than 1]

[modytrane]

attach a sample workbook with example.
modytrane

[ChemistB]

I'm not clear on what you are trying to do but if you reference a cell (lets say in row 2) and you insert a new row before it, the reference will automatically update. What is the specific problem?

[Joe Miller]

Please see the Excel attachment where The formula in Cell A1 is “=A4”.
My question is: When I insert a new row above row 4 and place a value in new cell A4, how do I make the value in A1 change to be the value which is in new cell A4?
And many thanks for your help and patience.
Joe Miller

[modytrane]

see attached file.
Use Indirect Function in A1.
modytrane

[Joe Miller]

Thanks Modytrane, that does exactly what I need.

For my future reference - is there a way to automatically pick up the top new row value if more than one row is inserted [eg] 15 rows inserted and then 15 values are pasted into the 15 new rows, and I want to pick up the new top row value?
[ie] I would like to be able to insert say the last 15 daily stock closing prices and not have to adjust any formulas. However, I can get along just fine with the solution you have already given to me.

[modytrane]

Look at the example attached.
It doesn't matter how many rows you insert.
With indirect function cell A1 will always look at Cell reference in J1.
As long as J1 is A4, A1 will be = A4.

Even if you add a column between A and J, the reference in A1 will adjuct for additional columns. Try it.

modytrane

[Joe Miller]

Damn, you're good.
Many many thanks
Joe Miller