Row insertion with downward propagation

[elbrus186]

Configuration: Excel 2003, XP (w/ SP3)

Here’s what I have on a spreadsheet:

A

11: some-text
12: some-text
13: some-text
14: some-text

My goal is to be able to insert a row somewhere between row 12 and row 14, so that when I change A11, the change will automatically propagate down without having to manually copy and paste.

Here are the cell contents:

A11: some-text
A12: =A11
A13: =A12
A14: =A13

However, when I do the following:

select Row 12, copy Row 12, perform “insert copied cells” on Row 12,

the result is the following:

A11: some-text
A12: =A10 <-- I want this to point to A11
A13: =A11 <-- I want this to point to A12
A14: =A13
A15: =A14


Any help/thoughts/hints would be greatly appreciated. Thanks!

[Alf]

What happends if write formula as

A11: some-text
A12: =INDIRECT("A11")
A13: =INDIRECT("A12")
A14: =INDIRECT("A13")

before you do the "insert copied cells"

Alf

[elbrus186]

Dear Alf,

Thanks for your reply.

Your solution would work, and actually all I would need is INDIRECT("A11") in each cell.

*B*U*T* (and this is my fault - I forgot one important fact)…

I need to be able to move the rows to a different location, every now and then. For example, I might move rows 11-14 to rows 61-64 (actually, to a different sheet).

Since the “A11” in the INDIRECT function is a literal, it will not update when I move to the new rows. In my 'move' example, I would need for INDIRECT("A11") to update to INDIRECT("A61").