Drag and Drop with formulas

**JakeMann** · 04-24-2013, 06:15 AM

Hello,

I have a selection of cells that has a value that is subtracting from a total value. There is a simple A1-B1 formula to take one value from the other.
If I drag and drop those cells into a different order I would like the formula to take this into account and subtract the new value when a drag and drop has been made. But for some reason when I do this the formula continues to look at the original cell.

For example,

A
1 - 1000

B
1 - 500 (formula = A1-B1) = 500
2 - 200
3 - 300
4 - 400

If I drag B3 into B1's position the formula should still subtract A1-B1 but produce a result of 700.

Any ideas on how to resolve this?

**AndyLitch** · 04-24-2013, 06:22 AM

Have you got $ signs in your formulas ?
They will absolute the function cell references ...

The other point is the difference between copy/paste and cut/paste ... copy changes the reference, cut doesn't

**JakeMann** · 04-24-2013, 06:31 AM

I've got the $ sign. It doesn't seem to alter the results of the formula when I move cells around though. In fact the absolute function doesn't seem to work how I'd expect as it's still altering the cell reference it's looking at.

**AndyLitch** · 04-24-2013, 06:35 AM

I think the function in B1 should read = $A$1-B1
And that should paste/autofill ok ... If not can you upload your sheet ?

**JakeMann** · 04-24-2013, 06:50 AM

I've attached the sheet I'm working on.
G27-G37 has the formula.

If I'm moving cells B30:F30 for example the formula should be reading the change in data bit it's not!