I am currently working with Excel and trying to establish a data connection from an Access Database. I have this process working properly, but the problem I am having is that once the data is imported, it throws off my formulas. This happens most noticeably when the connection is refreshed and new data is imported.
The most specific issue I have seen occur is that it impacts which cell the formulas reference. For example, it is supposed to be a statement such as "=If(C5=4)"...but once i import the data, it will change the "C5" to the first cell after the last piece of new data, which throws everything off.
I've attached a screenshot of what I mean. The screenshot was taken while I was using Excel 2007.
When you right click on the cell, you have to make sure that it is a cell within the query table - not just any old cell on the worksheet - otherwise you won't see the "data range properties" option in the menu. The example I used for the screenshot uses a query table to import a text file - this was just for convenience - I know you're not importing a text file, but the same property applies to your situation too.
If you don't have this option then you're not using a query table. If that's the case then you'll need to give us more information about how you are importing the data. I do know a workaround which can be applied to your formulas to prevent it happening, but it's really ugly so I'm hesitant to suggest it - it'd be much better to fix the import itself.