Prevent formula from changing when adding in cells

R

Rhett C

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
 
G

Gary''s Student

Tools > Options > Edit > clear the extend lists formats and formulas checkbox
 
R

Rhett C

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?
 
Top