Why does my formula change?

[gaj104]

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
	A	B	C	D
1	From this
2
3	100.00	300.00	(200.00)
4	100.50	350.30	(249.80)
5	200.00	500.00	(300.00)
6	600.00	600.00	-
7
8	To this
9
10	100.00		100.00
11		300.00	(350.30)	<--- Line insereted and amount dragged down
12	100.50	350.30	(399.50)
13	200.00	500.00	(400.00)
14	600.00	600.00	600.00
Sheet1

Anyone know how I fix this?

Thanks

 

[Aladin Akyurek]

Don't use drag and drop.

 

[gaj104]

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:

Worked out the answer:
=(INDIRECT(ADDRESS(ROW(),1)))-(INDIRECT(ADDRESS(ROW(),2)))

 

[Aladin Akyurek]

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:

Worked out the answer:
=(INDIRECT(ADDRESS(ROW(),1)))-(INDIRECT(ADDRESS(ROW(),2)))

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.