Connection Refreshed Changing Non-Connected Columns

[TXRChav]

I have a table that is connected to to data in another excel spreadsheet using Microsoft Query. The Query takes information in Spreadsheet 1 on the source Workbook and joins it to data on Spreadsheet 2 in the source Workbook then updates my table in the Report Workbook. I have re-arranged the columns of the report workbook since creating the connection, and I have added additional columns to the table for users to add their data to the related records.

My issue is that whenever I refresh the connection now it changes the values in the non-connected columns to formulas. Granted these formulas at one point were in these cells, but they are have since been replaced. But the refresh insists on reverting the cells to these formulas even though they are not in the query.

For those wondering: I know that my manual data won't line up after the refresh. I have a macro written to copy to copy the data into a separate spreadsheet and then insert an index match match formula into the cells to line up the old data. I then copy these columns and paste as values.

Coincidentally it is the index match match formula the refresh is trying to insert into my cells, but the problem is that it looses the reference to the array and produces an error.

Background Refresh is Disabled.

I am just refreshing the connection no refreshing all.

 

[Fazza]

not sure I fully understand, but it sounds like ...

there is a query setting that you can toggle for filling (or not filling) formulas that are in columns contiguous with returned query columns. how you change it (from a worksheet, not VBA) will be version dependent though it should be simple enough to find. in older versions just a right click from a query result cell and deselect filling down adjacent formulas, or whatever it is called.

this is a standard setting so if you don't find it straight away please google for it

OK?