If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Importing Access DB into Excel changes my calculated fields in Exc
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!)) |
#2
|
|||
|
|||
Importing Access DB into Excel changes my calculated fields in Exc
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 ata/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!)) |
#3
|
|||
|
|||
Importing Access DB into Excel changes my calculated fields in
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 ata/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!)) |
#4
|
|||
|
|||
Importing Access DB into Excel changes my calculated fields in
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 ata/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!)) |
Thread Tools | |
Display Modes | |
|
|