Say I have set up a data connection in Excel to update columns
A-G on a worksheet and sort on column A. If I put a formula in the column
H cells like
=SUM(B2:D2), then refresh the data connection, the column
H cell references can get messed up. For instance, the
H2 formula is now
=SUM(B7:D7) instead of
=SUM(B2:D2). I guessed the cause might be the data moving around in columns
A-G due to my sort on column A. But, changing the column
Hformula to use absolute row references -
=SUM(B$2:D$2) - still results in references getting messed up. It's as if Excel was moving entire rows of the spreadsheet rather than just the rows within the updated range. So my questions are:
1) Has anybody else seen this behavior?
2) If you have seen this behavior, do you know how to turn it off?