absolute addressing and inserting new cells

[Guest]

I'm trying to use the offset function to get a subset of a list that I update
by adding new cells and entering new data periodically. To make the
discription easy, my list is from A1 to A10, my offset function is in D1
through D5. The D column should relfect the most recent 5 entries in my A
column list. The offset is written as =offset($A$1,row(d1)-1,0) and is
copied down through all 5 cells.

This works just fine till I insert a new cell at A1. At that point, my
offset function in d1 gets changed to =offset($A$2......). Why does excel
change my absolute reference and how can I keep it from doing it?

 

[Ragdyer]

Try either of these:

=INDEX(A:A,ROWS($1:1))

=OFFSET(INDIRECT("A1"),ROW(D1)-1,0)

 

[Guest]

Both methods worked well for me. Thanks a bunch!

Just to complete the circle, I worked out a much less elegant workaround
that I ended up dropping in favor of the index('array ref'.... ) method. It
was to reference a header row cell in the original offset and then modifing
the row and column references accordingly. My data was added below the
headder row, so my references weren't effected by the insertion. Like I
said, though, the 2 methods you suggested are much better.

Flyswiftly.

 

[RagDyer]

Thanks for the feed-back.