Prevent formula from changing when adding in cells
 

Any help please...Trying to prevent my formula from changing in column D when
adding in cells to bring amounts in column B down to match up to column A
numbers. The columns are 800 to 1000 long, so it's not feasible to highlight
the column and cut and move, that's currently how we are doing it. In my
example, two checks 4.13 & 18.02(column A) were sent in to pay full amount
22.15 (column b) causing the false result and the negative amount in column c
and d. When I insert a cell to bring it down one row, the formula changes in
column d (=$a$9-$b$9) changes to (=$a$9-$b$10) even though the absolutes are
in the formula. Any ideas??? Thanks so much

$2.80 $2.80 TRUE $0.00 (=$a$1-$b$1)
$3.70 $3.70 TRUE $0.00
$31.00 $31.00 TRUE $0.00
$1.60 $1.50 FALSE $0.10
$8.98 $8.98 TRUE $0.00
$4.09 $4.09 TRUE $0.00
$8.30 $8.30 TRUE $0.00
$2.33 $2.33 TRUE $0.00
$4.13 $22.15 FALSE -$18.02 (=$a$9-$b$9)
$18.02 $31.30 FALSE -$13.28
$31.30 $6.90 FALSE $24.40
$6.90 $9.20 FALSE -$2.30
$9.20 $5.30 FALSE $3.90
$5.30 $57.11 FALSE -$51.81
$57.11 $3.80 FALSE $53.31
$3.80 $10.30 FALSE -$6.50
$10.30 $9.53 FALSE $0.77
$9.53 FALSE $9.53

Prevent formula from changing when adding in cells
 

Tools Options Edit clear the extend lists formats and formulas checkbox
--
Gary''s Student - gsnu200906


"Rhett C" wrote:

Any help please...Trying to prevent my formula from changing in column D when
adding in cells to bring amounts in column B down to match up to column A
numbers. The columns are 800 to 1000 long, so it's not feasible to highlight
the column and cut and move, that's currently how we are doing it. In my
example, two checks 4.13 & 18.02(column A) were sent in to pay full amount
22.15 (column b) causing the false result and the negative amount in column c
and d. When I insert a cell to bring it down one row, the formula changes in
column d (=$a$9-$b$9) changes to (=$a$9-$b$10) even though the absolutes are
in the formula. Any ideas??? Thanks so much

$2.80 $2.80 TRUE $0.00 (=$a$1-$b$1)
$3.70 $3.70 TRUE $0.00
$31.00 $31.00 TRUE $0.00
$1.60 $1.50 FALSE $0.10
$8.98 $8.98 TRUE $0.00
$4.09 $4.09 TRUE $0.00
$8.30 $8.30 TRUE $0.00
$2.33 $2.33 TRUE $0.00
$4.13 $22.15 FALSE -$18.02 (=$a$9-$b$9)
$18.02 $31.30 FALSE -$13.28
$31.30 $6.90 FALSE $24.40
$6.90 $9.20 FALSE -$2.30
$9.20 $5.30 FALSE $3.90
$5.30 $57.11 FALSE -$51.81
$57.11 $3.80 FALSE $53.31
$3.80 $10.30 FALSE -$6.50
$10.30 $9.53 FALSE $0.77
$9.53 FALSE $9.53

Prevent formula from changing when adding in cells
 

Thanks for your input on my problem but the formula still changes when I
insert a cell. I have already copied the formulas all the way down the column
d, but when I insert a cell in column b, it still changes the row ref in the
formula? Any other ideas?

"Gary''s Student" wrote:

Tools Options Edit clear the extend lists formats and formulas checkbox
--
Gary''s Student - gsnu200906


"Rhett C" wrote:

Any help please...Trying to prevent my formula from changing in column D when
adding in cells to bring amounts in column B down to match up to column A
numbers. The columns are 800 to 1000 long, so it's not feasible to highlight
the column and cut and move, that's currently how we are doing it. In my
example, two checks 4.13 & 18.02(column A) were sent in to pay full amount
22.15 (column b) causing the false result and the negative amount in column c
and d. When I insert a cell to bring it down one row, the formula changes in
column d (=$a$9-$b$9) changes to (=$a$9-$b$10) even though the absolutes are
in the formula. Any ideas??? Thanks so much

$2.80 $2.80 TRUE $0.00 (=$a$1-$b$1)
$3.70 $3.70 TRUE $0.00
$31.00 $31.00 TRUE $0.00
$1.60 $1.50 FALSE $0.10
$8.98 $8.98 TRUE $0.00
$4.09 $4.09 TRUE $0.00
$8.30 $8.30 TRUE $0.00
$2.33 $2.33 TRUE $0.00
$4.13 $22.15 FALSE -$18.02 (=$a$9-$b$9)
$18.02 $31.30 FALSE -$13.28
$31.30 $6.90 FALSE $24.40
$6.90 $9.20 FALSE -$2.30
$9.20 $5.30 FALSE $3.90
$5.30 $57.11 FALSE -$51.81
$57.11 $3.80 FALSE $53.31
$3.80 $10.30 FALSE -$6.50
$10.30 $9.53 FALSE $0.77
$9.53 FALSE $9.53