Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
absolute addressing and inserting new cells
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
absolute addressing and inserting new cells
Try either of these:
=INDEX(A:A,ROWS($1:1)) =OFFSET(INDIRECT("A1"),ROW(D1)-1,0) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "flyswiftly" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
absolute addressing and inserting new cells
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" wrote: Try either of these: =INDEX(A:A,ROWS($1:1)) =OFFSET(INDIRECT("A1"),ROW(D1)-1,0) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "flyswiftly" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
absolute addressing and inserting new cells
Thanks for the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "flyswiftly" wrote in message ... 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" wrote: Try either of these: =INDEX(A:A,ROWS($1:1)) =OFFSET(INDIRECT("A1"),ROW(D1)-1,0) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "flyswiftly" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|