# Why does my formula change?

#### gaj104

##### Well-known Member
Hi. I seem to have a problem. I have a simple formula subtracting from one cell from the other. However, when I insert a line and pull down one of the values the formula changes and references become offset.
Book1
ABCD
1From this
2
3100.00300.00(200.00)
4100.50350.30(249.80)
5200.00500.00(300.00)
6600.00600.00-
7
8To this
9
10100.00100.00
11300.00(350.30)<--- Line insereted and amount dragged down
12100.50350.30(399.50)
13200.00500.00(400.00)
14600.00600.00600.00
Sheet1

Anyone know how I fix this?

Thanks

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

##### MrExcel MVP
Don't use drag and drop.

#### gaj104

##### Well-known Member
Don't use drag and drop.

Thats is not what I call a solution! If I dont drag and drop I need to copy, paste then delete the line. This takes far more time, especially as I have to do this process about 50 times over.

I thought there might be a way doing it using the indirect or address function.

EDIT:

##### MrExcel MVP
gaj104 said:
Don't use drag and drop.

Thats is not what I call a solution!

You did not ask for a problem to solve. It was rather why do I get =A11-B12 instead of =A11-B11 you seem to have expected.

If I dont drag and drop I need to copy, paste then delete the line. This takes far more time, especially as I have to do this process about 50 times over.

I thought there might be a way doing it using the indirect or address function.

EDIT:

This is not an optimal solution either with all those volatile functions.

If you are on Excel 2003, just make of the data and processing area a list by means of Data|List|Create List. This functionality will correctly copy down the formulas automatically. Just insert a row where you need and enter the data. Subtracting will happen instantly with the right references.

Replies
3
Views
87
Replies
2
Views
215
Replies
5
Views
55
Replies
1
Views
70
Replies
3
Views
249