Hello,

I would like a cell, A1 say, to always show the value of the cell directly below.

i.e. typing '=A2' in cell A1.

If a new row is inserted below row 1, I still wish A1 to show the value of the values I will enter into A2.

I have tried both absolute and relative references but when I insert a new row below row 1 the value in A1 changes to A3.

Also when I set A1 to equal A2 and I type in a date in the cell below a numerical value appears instead of the date, how do I correct this?

s

This dumb board is not letting me do this with at least 10 more characters


=INDIRECT("A2")

=INDIRECT("A2")

Thanks for that, I think it works, however my data consists mainly of dates.

Cell A2 contains the date 03/11/2006 - 3rd November 2006

and when I use the indirect function I get the value 39024 in cell A1.

How do I get it to format dates properly.

Also if I click and drag the bottom right corner + symbol it copies the value A2 across all the cells rather than changing it to C2, D2, E2, etc. Is there a way to conveniently copy the formula to change with the corresponding columns?

Edit:

I have also just encountered another problem.

I want the same thing to happen on row 10 so in cell A10 I use the formula =INDIRECT("A11").

When I add another row above row 10 then I get a circular reference error as row 11 becomes the new row 10.

s

Okay, that is further than the original request and will take a little bit more.

When I add another row above row 10 then I get a circular reference error as row 11 becomes the new row 10.
Now do you want row 10 to still refer to 11 or row 11 to refer to row 12?

You could use the Offset function to achieve what you want if the latter, also as far as the date is concerned wont that be down to your cell formatting

Brian

Now do you want row 10 to still refer to 11 or row 11 to refer to row 12?


Row 11 to refer to Row 12.

i.e. the original row to always refer to the row directly below it.



You could use the Offset function to achieve what you want

Thanks, it works, the only thing is that if there is no value in the cell below I get 00/01/1900

s

=IF(ISBLANK(OFFSET(A1,1,0))," ",OFFSET(A1,1,0))

Brian