Excel Data Connection Updates Cell References Outside Table Range

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?


ANSWERS:


You simply need to extend the table to also cover column H! When you refresh your data, Excel differentiates between columns from the query and additional columns - and keeps the latter. At the same time, all columns get extended to whatever number of rows the query returns...



 MORE:


 ? How use loops by datetime each on weekly in vb.net
 ? Binding a GridView to a List
 ? Binding a conditional variable from an array
 ? Knockout.JS Bindinghandlers moment.js
 ? how can I databind a column to a datasource?
 ? How to set Binding of TextBox with DependencyProperty
 ? Data binding (AngularJS) inside JS
 ? How to add Swing Data Binding to class path?
 ? KnockoutJs doesn't react to custom events
 ? How to bind to a float2