Refreshing query changes cell references on a different worksheet that refer back to query worksheet. Why?

Question

Worksheet "Sheet_A" contains a query table positoned at cell A1. The top row contains column headings & the data, of course, begins in cell A2.

Worksheet "Ref_Sheet" contains numerous references to cells on "Sheet_A".

I created all references in row 2 of "Ref_Sheet" pointing to cells on row 2 of "Sheet_A".

I then "Filled Down" from row 2 to row 600 on "Ref_Sheet" so that each row on "Ref_Sheet" contains references to the same row number on "Sheet_A".

When I refresh the query on "Sheet_A" it pulls in 4 records, rows 2 - 5.

All references on row 2 of  "Ref_Sheet" now refer to row 5 of "Sheet_A", all references on row 3of  "Ref_Sheet" now refer to row 6 of "Sheet_A", etc.

In all, I have 9 queries on 9 separate sheets and 9 other "reference" sheets, each referencing cells from a different query sheet.

Some of the "reference" sheets maintain their references intact when their corresponding queries are refreshed, while others change the references as outlined above.

It appears that, for the sheets whose references change, the first row (row 2) on the "reference" sheet changes to reference the last row with data on the query sheet. This results in the "reference" sheet only showing one row of data, regardless of how many records were actually returned by the query.,

Why does the query-refresh change the relative refrencing on a different sheet?

Why does this not happen on every sheet?

How can I prevent the references from changing?

Answer 1

Using Structured References, rather than "Sheetname!C1" style references, resolved the problem.

Answer 2

As the file is macros enabled you may also post in below forum for better suggestion:

http://social.technet.microsoft.com/Forums/en/excel/threads

Answer 3

Thank you, Deepa.

Everything is in one workbook.

The format is .xlsm.

The problem occurs whether I refresh the queries via a macro or refresh them manually.

Changing the calculation method did not resolve the problem. The same references still changed.

In researching further, I found an Excel 2007 Help article entitled "Using structured references with Excel tables."

I had never used this type of reference; All my references were of the style "Sheet_A!C1".

I changed all my references to Structured References and it appears to have solved the problem (pending a few more tests).

Answer 4

What is the format of the Excel file, is it .xls or .xlsx?

Is the ‘Ref_Sheet’ in the same workbook or different workbook?

Method 1:

· If it is on a different workbook, you may open the Excel file > View > New window > Save the new instance of file with different name.

· Try to open the new Excel file and check if the issue reoccurs.

Method 2:

· If the above Method fails then you may also set the calculation to manual and check the result.

· Open Excel file > Click on Office button > Excel options > Formulas > Workbook calculation > Manual.