One way which does it ..

In Sheet5,

Place this in the starting cell (where you had: =Sheet4!A2)
eg in say, B2:
=OFFSET(Sheet4!$A$2,ROW(A1)-1,COLUMN(A1)-1)
Just copy B2 down (or down and across) as far as required to return the
equivalents of the simple link formulas, but with the required flexibility
that new row insertions in Sheet4 will now be automatically catered for. Test
it out and see for yourself.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Priscilla" wrote:
I am trying to figure out how to insert data in between existing data in one
column and have this data automatically populated into another worksheet
column.

The data is populated properly when I add the new entry into the last row in
the column and when I make modifications to the data. However when I insert a
new row in between the existing data the new entry is not picked up. I am
forced reset the formula in that column worksheet in order for the new data
to be picked up. Is there a way to get this type of insertion picked up
automatically?


I have the following set in Insert Name Define in Sheet4 Column A:
=OFFSET(Sheet4!$A$2,0,0,COUNTA(Sheet4!$A:$A))

I have the following formula in Sheet5 column A : as listed below

For example:
Sheet4 Sheet5
Red red (formula is =Sheet4!A2)
White white (formula is =Sheet4!A3)
Black black (formula is =Sheet4!A4)

If I insert blue prior to black in sheet4 I will not see this entry in Sheet
5.




--
Priscilla

For a neater look in Sheet5,
we could suppress the display of extraneous zeros in the sheet
via clicking Tools Options View tab, Uncheck "Zero values" OK
(as zeros will be returned for blank source cells in Sheet4)

Or alternatively, we could use instead in B2:
=IF(OFFSET(Sheet4!$A$2,ROW(A1)-1,COLUMN(A1)-1)=0,"",OFFSET(Sheet4!$A$2,ROW(A1)-1,COLUMN(A1)-1))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thank you very much. That resolved the issue and gave me a better
understanding of the offset funtion
--
Priscilla


"Max" wrote:

One way which does it ..

In Sheet5,

Place this in the starting cell (where you had: =Sheet4!A2)
eg in say, B2:
=OFFSET(Sheet4!$A$2,ROW(A1)-1,COLUMN(A1)-1)
Just copy B2 down (or down and across) as far as required to return the
equivalents of the simple link formulas, but with the required flexibility
that new row insertions in Sheet4 will now be automatically catered for. Test
it out and see for yourself.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Priscilla" wrote:
I am trying to figure out how to insert data in between existing data in one
column and have this data automatically populated into another worksheet
column.

The data is populated properly when I add the new entry into the last row in
the column and when I make modifications to the data. However when I insert a
new row in between the existing data the new entry is not picked up. I am
forced reset the formula in that column worksheet in order for the new data
to be picked up. Is there a way to get this type of insertion picked up
automatically?


I have the following set in Insert Name Define in Sheet4 Column A:
=OFFSET(Sheet4!$A$2,0,0,COUNTA(Sheet4!$A:$A))

I have the following formula in Sheet5 column A : as listed below

For example:
Sheet4 Sheet5
Red red (formula is =Sheet4!A2)
White white (formula is =Sheet4!A3)
Black black (formula is =Sheet4!A4)

If I insert blue prior to black in sheet4 I will not see this entry in Sheet
5.




--
Priscilla

Glad it helped, Priscilla !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Priscilla" wrote in message
...
Thank you very much. That resolved the issue and gave me a better
understanding of the offset funtion
--
Priscilla