See me answers:
1)"When I refreshed this new year, less data is returned" - check your
access data base,. it it possible that some query doesn't work properly. You
may new codes that the query doens't capture. Go to the source and check it
there.

2)"the calculated fields where there is no data returned are showing #REF""
- One of the cause of this is that you are using a link to a program that is
not running. It is possible that somebody moved Ms Access database somewhere
else? (other cause is when you try linking to a Dynamic Data Exchange (DDE)
topic such as "system" that is not available)
See how it has been linked to your excel file :Data/Import external
data/edit query

You may also insert a new sheet, link it again and see if you still have a
problem
--
Click yes if helped
Greatly appreciated
Eva


"Shadow27_us" wrote:

I currently have an Excel worksheet that imports data from MSAccess. I have
calulated fields built into this Excel sheet that calculates info from the
results that are returned. When I refreshed this new year, less data is
returned and the calculated fields where there is no data returned are
showing #REF!. How do I keep the calculated fields static so they don't try
to change when new data is brought in and some fields are now blank? See
example below. The calculated fields are Variance and Pct Change

Columns
Year Month Dollars1 Dollars2 Dollars3 Total Variance Pct Change

Data
2008 December 500 500 100 1100 =IF(F4 ="","",SUM(F3-F4)) =IF(G3
="","",SUM(G3/F4))


When I hit refresh, the Variance and Pct Change columns that returned data
prior to refreshing but now since it is a new year don't return any data now
look like this.

Variance
=IF(#REF! ="","",SUM(F25-#REF!))

Pct Change
=IF(G25 ="","",SUM(G25/#REF!))