1

In Excel, I have a querytable linked to an SQL table. Formulas inside of my spreadsheet contain references to the cells inside the table, using absolute references such as Z9.

Before Refresh

I use vba to update the SQL query for the table. The trouble starts when I change the column that I pull from SQL. The cell references break, even though the data I am interested in is in the same cell.

After Refresh

I obviously run into the same problem if using the =Table1[Column1] reference methodology because the column name changes. Is there a way to change a column in a querytable without breaking sheet formula references to that column?

  • 1
    I don't think this is supposed to happen. If you post your code we may be able to help you debug. – Kyle Mar 1 '16 at 1:37
  • Have you tried indirect references INDIRECT or OFFSET? You shouldn't have issues with that. – Máté Juhász Mar 1 '16 at 16:12
  • I think those are solutions, but they are inferior and should only be used if your data is so large that a full query represents a drag on your sheet. Formulas with INDIRECTwould have to be updated manually and laboriously if the sheet is altered even slightly eg column added, ranges moved etc. OFFSET is a volatile function, which can hurt sheet calc time. – rvictordelta Mar 2 '16 at 23:27
0

I was not able to find a way to prevent #REF breaks when my vba code changed the SQL query. I was able to work around the error though! Instead of changing the query based on user inputs, I pulled the entire set of data in one go and then used index-match lookups on this universal set to grab the desired column.

A typical excel solution: functional, but inelegant.. Will select your answer if you have something better!

0

I ran into a similar problem when downloading data from a website, using the QueryTables.Add Method. References to the sheet that contained the query data kept changing whenever I would re-execute the query. I changed the approach to copy and paste data from the query sheet into the other sheet. At first that did not work either, as the from values changed columns with subsequent queries. To resolve this, I did the following:

  1. Cleared the query worksheet before issuing the query.
  2. Reduced the query to retrieve only essential data, about a 20-fold reduction.
  3. Used the following code to do the Copy/Paste:

    Sheets("Data").Cells(FromRow, FromCol).Copy
    Destination:=Sheets("Port").Cells(ToRow, ToCol)
    

This seems to work. Subsequent queries that populated the "Data" sheet were correctly pasted into the "Data" sheet. I suspect that there was a synchronization problem when the query was large, with references in the other sheet being executed before the query completed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.