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

0
Utf
1/7/2010 6:40:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1093 Views

Similar Articles

[PageSpeed] 35

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!))
> 
0
Utf
1/7/2010 9:40:01 PM
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!))
> > 
0
Utf
1/7/2010 9:56:09 PM
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!))
> > > 
0
Utf
1/7/2010 10:10:05 PM
Reply:

Similar Artilces:

Emailing in excel 2003 02-26-10
If i type in the cell A34: neil.Holden@test.com and press a button is it possible to email to the address of what ever is in A34 is? The email body should say: this has been submitted for cell B34 and todays date. Thanks. Check out Ron De Bruins "Send-Mail" tips: http://www.rondebruin.nl/sendmail.htm Micky "Neil Holden" wrote: > If i type in the cell A34: neil.Holden@test.com and press a button is it > possible to email to the address of what ever is in A34 is? > > The email body should say: this has been submitted for cell B34 and...

Outlook Mobile Access Problem
Hi I've a big problem in Outlook Mobile Access. I've a server exchange standalone withoput a front-end=20 one... I've configured the OWA web form authentication and SSL. For OWA it's all right...everything is going right... The problem is OMA When I try to log on on oma I find the following error=20 message :"Unable to connect to your mailbox" To solve my problem I've found in microsoft web site the=20 following link Microsoft Knowledge Base - 817379 Cannot Access Exchange Server 2003 by Using Outlook Mobile=20 Access When the Exchange Virtual Directory Require...

VBA from another app: Suppressing Excel confirmation dialog?
After creating/formatting several worksheets from MS Access, I'd like to delete the "Sheetn" worksheets that got put there when I did a .WorkBooks.Add. I avoided using them because I'm not sure how/why they are created - i.e. maybe some user's defaults would only create 1 empty sheet or none. So, form MS Access's VBA I'd like to do: On Error Resume Next .Worksheets("Sheet1").Delete .Worksheets("Sheet2").Delete .Worksheets("Sheet3").Delete .Worksheets("Sheet4").Delete On Erro...

Importing Data into an Excel Pivot Table via Access
I have set up a query in Microsoft Access which is linked to our AS400 server. I have created pararmeters within Access which asks for certain fields which works. I then go into Excel and create a pivot table with the external data source that I have created in access. When I go to enter a pararmeter within Microsof Query I get a reply saying that "Parameters can not be used with this Query", what I want to do is setup a parameter on the Excel spreadsheet which then goes and gets the data i require from this parameter. I would be very grateful if someone could help me with thi...

how do I add times in Excel and result in hours & mins
I want to insert a time when I start work and a time when I take a break, then a time when I leave work. Following that I want to be able to add up the amount of hours that I have worked. This will enable me to plan my week ahead and ensure I only allocate a specific amount of time to a project. http://www.cpearson.com/excel/datetime.htm#WorkHours -- Kind Regards, Niek Otten Microsoft MVP - Excel "Rty Shaw" <Rty Shaw@discussions.microsoft.com> wrote in message news:37D03D72-5525-4D6E-8ED7-2911B16248B0@microsoft.com... >I want to insert a time when I start work and...

Illegal operation error while printing EXCEL or WORD Files
Hi, I am facing an illegal operation error when i try to print any file from excel (any no. of pages), this happens in stand alone printer as well as a networked printer. When we press the print button, it flashes this message, but still prints, but once the printing is completed, i will have to restart the PC. Due to this error other applications PRINTING also will NOT HAPPEN and the only way out is, restart the PC. This happens not only in EXCEL, it happens in all the MS applications (outlook, access, front page, powerpoint also). When I check the print manager (before restart),...

Automatic Calculation #2
Why is automatic calculation keeps running even though there is no modification done in my spreadsheet? Thanks, Lucie Ch�nier What makes you think that excel is recalculating? If it's because you see Calculate in the statusbar, it could be... http://support.microsoft.com/default.aspx?scid=243495 XL: Calculate Message Remains in Status Bar If 65,536 Formula References "Lucie Ch�nier" wrote: > > Why is automatic calculation keeps running even though there is no > modification done in my spreadsheet? > > Thanks, > > Lucie Ch�nier -- Dave Peterso...

Opening a new instance of Excel
I am using multiple monitors for work and it is great! Is there a setting that I can use so that it opens each new excel file in a new excel window so I can drag different ones to each monitor? Is there a similar setting for Word? I am using Excel 2002 and Word 2002. Thank you. Hi, Yes, you can check the Windows in Taskbar checkbox in Tools; Options. This is on the View tab for both Word and Excel. >-----Original Message----- >I am using multiple monitors for work and it is great! Is >there a setting that I can use so that it opens each new >excel file in a new excel ...

allowing or disallowing changing the state of check box
Hi, what's the best moment to check if the user has right to change the state of check box and then to allow it or cancel it. I've tried with "before update" event procedure. It seems to work but after, when I press anything else on the form event procedure is repeating? Any help? thanx alekmil One addition: The problem occurs (repeating) when Cancel = True was applied previosly. "alekm" wrote: > Hi, > what's the best moment to check if the user has right to change the state of > check box and then to allow it or cancel it. I'...

Excel Edit F2 button changed for Mac???
Switched to Microsofts version of Excel for Mac. Can anyone tell me what keystroke allows me to edit a cell? Before I switched to a Mac it was the F2 button. Please help. Thank you. See the answers in the m.p.mac.office.excel newsgroup. In article <1176582208.958694.269620@q75g2000hsh.googlegroups.com>, ssears@indy.tds.net wrote: > Switched to Microsofts version of Excel for Mac. Can anyone tell me > what keystroke allows me to edit a cell? Before I switched to a Mac > it was the F2 button. Please help. Thank you. ...

EXCEL TROUBLESHOOTING #2
I have an excel file (2000 format), that after I made a number of changes is causing me problems when I re-open the file. Windows task manager goes to 100% CPU activity, and i cant do anything within the excel file. However, if I set recalculation to manual before I open the file, all seems fine. Obvioulsy I have a problem. But how do i find that problem ? Thanks in advance. I have had some experience running large spreadsheets lately. Above a certain size, the recalculation time seems to climb very fast. While Excel is recalculating, you can't do anything anyway. Best in my v...

Excel Crash
I use Excel and Word 2003 using Windows NT. I've kept some files on a jump drive so I can work on them at home. I attempted to work on a Word documents which had an Excel worksheet inserted in it. I tried double clicking on the worksheet to edit it and Word and Excel shut down. Now when I attempt to open Excel at home it asks for my Office XP Professional installation cd. (I have Office XP at home with Windows XP). I'm having a hard time locating my original discs. Does anyone have any suggestions or experience anything like this? ...

cmbo box on subform fills fields on form when chkbx is yes
I have a multi-tabbed form that details bid data ranging from pricing to contact info to departmental notes. Page two has a subform listing general contractors to whom we bid. One bid may have 10-15 contractors listed. When a purchase order is issued from one of these contractors I select a checkbox indicating that GC. I would like the opening page on this tabbed form to show the address and contact info for that GC when the checkbox is yes. Is this possible? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200803/1 On Mar 17, 3:04=A0p...

DB Emailing Questions
1) How do i format the message text of an email in Access. I used macro builder - action = sendobject. Can you use code builder? if so, please give me an idea of how the code will be like. 2) Also can i assign the email to have a recipient automatically or do i have to write it down each time? Thank you in advance. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201001/1 On Jan 21, 11:51=A0am, "axm0035 via AccessMonster.com" <u56602@uwe> wrote: > 1) How do i format the message text of an email in Acce...

Import Palm files
Can anyone help me by telling me how to import palm files (dat files) into outlook. I want to import contact, calendar, etc Depends what you can export to from Palm Desktop. Personally, I've found this messy. Why not just get sync software that can sync your Palm with Outlook directly? Chapura Pocketmirror or Pumatech Intellisync are two ideas....or your Palm CD may have come with this option. F. Carvalho wrote: > Can anyone help me by telling me how to import palm files > (dat files) into outlook. I want to import contact, > calendar, etc ...

Does anyone have a dashboard gauge (speedometer style) for Excel?
I am trying to create dashboard charts from Excel data and would love other templates not available in Excel today - speedometer charts, multi-dimension comparitive charts, charts that build information overlays. I regularly create these in a manual way for executive and customer summaries but would appreciate the ability to automatically generate these types of charts allowing for real time viewing of "what if" scenarios. Steve, there are tons of these things out there to review, few better than this collection: http://www.andypope.info/charts.htm Andy Pope has put together...

Globally change part of a field
I have a text field that holds the file location of a picture. The content looks like this: H:\Access Data\Personnel Pictures\picturefile.bmp The "picturefile.bmp" is different for every record. Everything preceding it is identical in all records. I've moved the folder with all the pictures to a new location and need to update this field to the following: S:\Everyone\Member Database\Personnel Pictures\picturefile.bmp So basically, I need to swap out "H:\Access Data" for "S:\Everyone\Member Database" in every record. How so? Thanks in advance. Jer...

connect a form to excel database
I have a database in excel which lists our stock and prices etc. I would like to be able to enter a stock number on an invoice that will retrieve the details and price from my excel database. Can this be done? Excel has built in option--try data|form. If that doesn't quite work, but it's close, John Walkenbach has an enhanced dataform at: http://j-walk.com/ss/dataform/index.htm And if you want to design your own, Debra Dalgleish has a get started with userforms: http://contextures.com/xlUserForm01.html Kathy wrote: > > I have a database in excel which lists our stock and p...

Does anyone know where I can download EXCEL.MSI?
I am missing my Excel 2002 CD, and can't install Office Update Service Pack 3 without this file. ...

Excel ignores boot-time regional settings when interpreting a date
I have a third party DDE app that exports dates as strings, correctly using the short date format in the regional and language settings, which, in my case is MM/DD/YYYY (reset at every boot). Unfortunately excel insists in interpreting that date as DD/MM/YYYY [Application.International(xlDateOrder)=1, it should be 0], disregarding my regional settings. The result is that all the dates are wrong or #VALUES! If this is not wierd enough, listen to this: it only happens after I reboot (when the date format is reset to MM/DD/YYYY). If subsequently I change the short date format in the regional se...

Excel to check for regular expression?
Hi, How do you use Regular expression in excel? For eg: Check a column of data to see whether are all of them valid email addresses? Please advise THanks By valid, do you mean that the email is in proper syntax or that it is an actual email address. Text formulas can be used for proper syntax. Perhaps a Visual Basic proceedure. I doubt you can check that the email address actually exists, as many companies and ISPs will simply disregard and drop mail to invalid email addresses to eliminate spam "user" wrote: > Hi, > > How do you use Regular expression ...

Contact not importing names of csv lists
Whenever I import a csv file into outlook 2007 it imports only the email addresses, nothing else. I've titled the csv columns with the exact same headings as the outlook contact headings. Still, it imports only the email addresses. What do I need to do to get it to import the full names as well? Is it correct to assume that because you stated that you "titled the csv columns the same as the Outlook contact headings" - that you used "auto-map" to import your data? There is absolutely no reason, benefit or value whatsoever (under any circumstances) in ...

Changing Posting Settings
I just wanted to double check that if I am currently Posting TO General Ledger in the Purchasing Series, if I change that to Post THROUGH General Ledger will this have any ill effects. I would make sure everyone is off the system, however if there are batches in the GL that were generated by the AP subledger is this ok and would it then post through the general ledger for any new transactions posted once I change this option? You are fine. Manual post any GL batches. You can do this before or after you change the option. "VDebattista" wrote: > I just wanted to double che...

Excel Cells Contents to HTML Pages
Hi, I have an excel sheet which has two columns. Column 1: PageName Column 2: HTMLCode PageName has information like: OurServices.htm which is the name of th HTML page. HTMLCode clumn has actual HTML Code like: <html> <head> <meta http-equiv="Content-Language" content="en-us"> <meta name="GENERATOR" content="Microsoft FrontPage 5.0"> <meta name="ProgId" content="FrontPage.Editor.Document"> <meta http-equiv="Content-Type" content="text/html charset=windows-1252"> ...

In Excel program shows a broken line and don't know how to get ri.
I am trying to put in entries in Excel program and when I go to Print Preview, it only shows part of the entries I put in. When I go to the last entry that it shows in Print Preview, there is a broken line between it and the next entry. I don't know what I did. I could use some help You probably have multiple areas selected for your print area. you can check this by <view><page break preview> One possibility to correct this would be <file><print area><clear print area> or specifically select the area you want printed and <file ><Print area>...