Maintain Formula Reference (sort of)
 

Hi,
If I have a simple speadsheet:
A B C D
1 1 2 3 (formula is =A1+B1)
2 3 4 7 (formula is =A2+B2)
3 5 6 11 (formula is =A3+B3)

Column D simply adds up the number from A1 and B1, etc. However, what I
want to be able to do is, for example, cut cells A3&B3 and "Insert Cut Cells"
over, say A1&B1, so that the values in row 3 are moved to row 1 and the rest
of rows each shift down one. However, when I do this, the formulas in column
D maintain their original reference. In other words, the spreadsheet becomes:
A B C D
1 5 6 3 (formula is =A2+B2)
2 1 2 7 (formula is =A3+B3)
3 3 4 11 (formula is =A1+B1)
But this is wrong because I need colum D to ALWAYS be the sum of the A and B
values from the same row. Is there a way to format these formulas so that
regardless of where I "move" the data, the formula always sums the vaules in
the same row? Thansk in advance.

Maintain Formula Reference (sort of)
 

Hi Jim
One way
=INDEX(A:A,ROW())+INDEX(B:B,ROW())
copy down as far as required
--
Regards

Roger Govier


"JimK" wrote in message
...
Hi,
If I have a simple speadsheet:
A B C D
1 1 2 3 (formula is =A1+B1)
2 3 4 7 (formula is =A2+B2)
3 5 6 11 (formula is =A3+B3)

Column D simply adds up the number from A1 and B1, etc. However, what
I
want to be able to do is, for example, cut cells A3&B3 and "Insert Cut
Cells"
over, say A1&B1, so that the values in row 3 are moved to row 1 and
the rest
of rows each shift down one. However, when I do this, the formulas in
column
D maintain their original reference. In other words, the spreadsheet
becomes:
A B C D
1 5 6 3 (formula is =A2+B2)
2 1 2 7 (formula is =A3+B3)
3 3 4 11 (formula is =A1+B1)
But this is wrong because I need colum D to ALWAYS be the sum of the A
and B
values from the same row. Is there a way to format these formulas so
that
regardless of where I "move" the data, the formula always sums the
vaules in
the same row? Thansk in advance.

Maintain Formula Reference (sort of)
 

"JimK" wrote in message
...
Hi,
If I have a simple speadsheet:
A B C D
1 1 2 3 (formula is =A1+B1)
2 3 4 7 (formula is =A2+B2)
3 5 6 11 (formula is =A3+B3)

Column D simply adds up the number from A1 and B1, etc. However, what I
want to be able to do is, for example, cut cells A3&B3 and "Insert Cut
Cells"
over, say A1&B1, so that the values in row 3 are moved to row 1 and the
rest
of rows each shift down one. However, when I do this, the formulas in
column
D maintain their original reference. In other words, the spreadsheet
becomes:
A B C D
1 5 6 3 (formula is =A2+B2)
2 1 2 7 (formula is =A3+B3)
3 3 4 11 (formula is =A1+B1)
But this is wrong because I need colum D to ALWAYS be the sum of the A and
B
values from the same row. Is there a way to format these formulas so that
regardless of where I "move" the data, the formula always sums the vaules
in
the same row? Thansk in advance.

In D1, try =OFFSET(D1,0,-3)+OFFSET(D1,0,-2)
--
David Biddulph