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!))

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!))

Thanks Eva. That got my mind thinking. I figure out how to fix this so I
thought I would share with all in case someone in the future has the same
issue. I right clicked on my cell that is linked to the MS Access database
and choose Data Range Properties. In there under "If the number of rows in
the data range changes upon refresh:" I had "Insert cells for a new data,
delet unused cells" checked. I should have check the second option which is
"Insert entire rows for new data, clear unused cells". Also, I didn't have
"Fill down formulas in cells with new data, clear unused cells" checked.
Once I changed these two things, all it good.

"Eva" wrote:

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!))

That is a good inside. I was looking at data range properties for a moment:)
It is helpfull hint for the future ( I also have number of excel files linked
to access databases)
--
Greatly appreciated
Eva


"Shadow27_us" wrote:

Thanks Eva. That got my mind thinking. I figure out how to fix this so I
thought I would share with all in case someone in the future has the same
issue. I right clicked on my cell that is linked to the MS Access database
and choose Data Range Properties. In there under "If the number of rows in
the data range changes upon refresh:" I had "Insert cells for a new data,
delet unused cells" checked. I should have check the second option which is
"Insert entire rows for new data, clear unused cells". Also, I didn't have
"Fill down formulas in cells with new data, clear unused cells" checked.
Once I changed these two things, all it good.

"Eva" wrote:

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!))