M
mgilberg
Here is a simplistic explanation of the problem.
Suppose I have a simple formula in A3: =A1+A2
I drag row 1 to row 4. The formula in A3 adjusts to: =A4+A2
Suppose I have the original formula in two columns.
Cell A3: =A1+A2, Cell B3: =B1+B2
I drag row 1 to row 4, all formulas adjust to...
Cell A3: =A4+A2, Cell B3: =B4+B2
Suppose I have the original formula in 1000 columns.
When I drag row 1 to row 4, the formulas DO NOT ADJUST!
The formula in A3 is still =A1+A2, even though cell A1 has moved to A4.
This is a simplified description. In fact, when the formulas are as simple
as described above, they adjust correctly when the row is moved. However, I
have some worksheets with more complicated formulas where there is a
threshold of data to move... if you cross the threshold, the formulas do not
adjust. Below the threshold, the same move results in correctly adjusted
formulas. There's no warning, you find out later.
I can send specifics, but in general this seems to be an Excel bug.
Suppose I have a simple formula in A3: =A1+A2
I drag row 1 to row 4. The formula in A3 adjusts to: =A4+A2
Suppose I have the original formula in two columns.
Cell A3: =A1+A2, Cell B3: =B1+B2
I drag row 1 to row 4, all formulas adjust to...
Cell A3: =A4+A2, Cell B3: =B4+B2
Suppose I have the original formula in 1000 columns.
When I drag row 1 to row 4, the formulas DO NOT ADJUST!
The formula in A3 is still =A1+A2, even though cell A1 has moved to A4.
This is a simplified description. In fact, when the formulas are as simple
as described above, they adjust correctly when the row is moved. However, I
have some worksheets with more complicated formulas where there is a
threshold of data to move... if you cross the threshold, the formulas do not
adjust. Below the threshold, the same move results in correctly adjusted
formulas. There's no warning, you find out later.
I can send specifics, but in general this seems to be an Excel bug.