CODEWITHSUNDEEP
exceldata-binding
B H
B H

Reputation: 1878

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?

Upvotes: 0

Views: 3166

Answers (1)

Peter Albert
Peter Albert

Reputation: 17485

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...

Upvotes: 1

Related Questions