Need to force a formula reference to stay the same even after query refreshes.

[9tanstaafl9]

I have the following formula on the first tab of my workbook:

='Original Contract'!C$2

The cell C2 is part of a query that gets refreshed when my macro runs. The query only ever has one row plus the header row. It is never blank. The data just changes depending on which job number they are running it for.

For some reason, and only for one of my 100 or so customers using this report (but on all of his computers), after the query runs the formula changes to be:

='Original Contract'!C$3

The report is now wrong, because the data we need is still in the second row. There are a few other formulas that refer to that tab and query and they all experience this issue. Other formulas referring to other queries on other tabs are not having this issue. Also, this customer had no issues prior to a month ago.

I tried fixing this by using a named range, but the only thing that happened was my named range moved down a row and was attached to a cel with no data.

I realize I could solve this problem by having my macro just recreate the correct formula. But I am more concerned as to why something like this could happen in the first place. The particular formulas in this case were easy to notice because that query only ever results in one line of data. If this is just an issue that can happen whenever it wants to, I'd be concerned that my reports are giving people data that is one line off and possibly not noticing it.

Any ideas?


If it matters, I have run this report on every prior version of Excel for the past 10 years and it has worked fine. I'm using the current version and it works fine on my computer. I have SEEN it not work on theirs. They use Excel 2010. The query in question connects as a Visual Fox Pro table. Background refresh is NOT enabled. The box to preserve data formatting/column/sort layout is checked.






[TABLE="width: 375"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[9tanstaafl9]

Bump! Also I've gathered more info.

I walked through the macro line by line, and the problem happens on the line where the queries refresh.

I tested other copies of the report on the same machine. (They run the same report for different jobs and keep copies of each.) On the reports that are broken, no matter what job you run the formula reference moves down a cell when you refresh the query. On the copies of the reports that don't have a problem this doesn't happen no matter what job you run.

Sooooo. I know it is a problem with the data, or the ODBC connection because those are the same for both reports. It isn't an issue with the macro or the formula, those are the same too. It isn't a problem with add ons, or with other concurrently running programs or it would've happend on all the reports I ran that day.