Micorsoft Data Query shifting nearby formula cell references

[krazyderek]

I have a two data query's that are set to auto update when you open the workbook.
Both query's are going to the same sheet

Query 1 is in columns A:I
Query 2 is in columns J:Q

Columns R:W calculate some values based on the data from both queries, i then used auto fill to populate the formula's down 10000 cells in columns R:W

When the query's refresh using the automatic refresh on open they affect the cell references in the calculating cells.

For example, if i have 200 rows of data from the query, then close and open the workbook, and it grabs another 150 rows of data from the query's i would expect that the calculating cells in columns R:W to continue to reference the cells on their row but around row 300 they reference will jump to reference a cell in row 750, and all the calculating cells below it will also have jumped or been offset the same.

If i manually right click the query's and press refresh, the problem doesn't happen.

I've used auto fill to simply redo the formula's after this happen's, but it's getting to be a pain and i wanted to get to the bottom of it.

 

[GlennUK]

Change the External Data Properties. You probably have "Insert Cells For New Data" option. Change it to "Overwrite Existing Cells", and see if that fixes things.

 

[krazyderek]

Change the External Data Properties. You probably have "Insert Cells For New Data" option. Change it to "Overwrite Existing Cells", and see if that fixes things.

Thanks, Glenn, completely missed that, there's even a check box to auto fill the formula too, beauty!