A dept here uses past excel sheets to see how the budget would have changed if they had done y instead of x.  So the file that was created in January needs to keep the values in that sheet until it is modified by the user or told to update.

The spreadsheet links data from a 3rd party application.

In Excel 2007, everything works fine.  You open the file, the values are all there the same as the last time you saved it.

In Excel 2010, the sheet updates when the file is opened.  You can tell this by comparing a cell value that is now listed as #Ref in 2010 that has a value in 2007. 

I have tried to turn off auto linking but cannot find a value that covers all of Excel.  Some posts I have found suggest doing it for a workbook, but by the time I am able to do that the value has already been updated and saving it becomes useless.

 

Any ideas?

 

Mike

Try going to Excel Options-->Trust Center-->Trust Center Settings-->External Content-->Prompt user on automatic update for Workbook Links

 

That is the default setting for us. 

One odd part is that over half of this sheet I am looking at now have values that are pulled from the external application.  Only one line of values has the #REF! message.  There are some other cells that have values in them if I pull it up in 2007, but the cells are blank in Office 2010.

The user that first found this issue does have the third party application installed, but I do not and can replicate his issue..

I'm not really sure what to say here.

I have seen some instances in Excel 2010 where, despite being set to automatic calculation, that I have to force manual recalcs.  In my case these are massive files that make use of UDF's from an add-in.  It shouldn't be an issue, but for some reason it is.

Sounds like this is opposite to my issue though.  While I haven't come across a problem exactly like this one my usual troubleshooting steps when I've got weird things happening is to:

• Disable/uninstall any Excel add-ins (including COM add-ins)
• Uninstall any 3rd party apps that I installed recently which may impact Excel
• Run a repair on the Office installation

Beyond that I'm not sure what else to offer...

I just tried running an upgrade on an installation of Office 2007 to 2010 on a spare computer.  I disconnected the laptop from the network so it wouldn't get updates.  Same issue came up.

Just to show you what I see here is a snip of the same 6 cells.  First in 2007, then in 2010.

I confirmed on both mine and the computer I just the upgrade to 2010 on that there are no add ins installed.  No recent 3rd party applications on the spare I am testing with.  Just did the installation, so I may try to do a fresh install.  Although the user that found the issue is working on a fresh install of everything due to a laptop refresh.

Thank you for your efforts.

Out of curiosity, what are the contents of those cells?

Forum ate my first post, ugh.

The formula in the cell is =FSDDE|Quote!'fld:"XPH U1"[LABEL].LAST'

Where LABEL is a formula/value from the 3rd party application. 

I compared the formulas between the 2 excel version and they show up the same.  The left column should have values populated throughout, but is empty on 2010.  In 2007 there are values there.

Out of curiosity, have you checked with the 3rd party vendor to see if they have seen this issue before?  Do you know for certain that they support Excel 2010 with their product?

I have not yet, because if we want the data to up to date it works as intended.  Everything updates fine. 

The third party application is called Futuresource Workstation and in this case acts as nothing but a data feed for this spreadsheet (when we are trying to update the data)

What I am trying to do is have Excel not update the data in the spreadsheet.  If the data was not updating I could see having to contact the vendor, but the issue is excel is automatically updating links when everything is pointing that it should not. 

Right, but consider this...

When I built the addin I mentioned above, I programmed several user defined functions (UDFs).  In layman's terms, I programmed my own functions to use in the Excel grid.  So my worksheets now contain my custom functions like =GLDATA(x,y,z)

When creating the UDF's, I never put in any effort to checking if the application was set to prompt users for updates on their links.  Instead, the standard calculation triggers are what fire my UDF's to recalculate.  So in my case, disabling links completely would have no effect whatsoever.  Disabling macros, however, would stop the recalc.

I suspect that Futuresource Workstation may be working similarly.  It may never have occured to the developers that someone wouldn't want to automatically update their numbers, so they may have ignored that check.

The real rub is that removing the addin would probably give you #NAME? errors, and they may be no way to stop the recalc short of doing that or pasting the formulas as values first.  Neither one is really going to be a workable solution long term.

If you're not using comments for anything in your workbook, and this is absolutley critical, you could put in a workaround to do the following:

Every time the workbook is saved, you could

• Write any formula containing a Futuresource function to a comment
• Paste the formulas as values to avoid it updating

You would also then need to make sure that every time the workbook was activated that

• The comments contents were pasted back into the cells as formulas to allow recalc

The key to this is that you would need to create a list of users that either had the option to calculate or not, and take that into account when firing the past routine.

Unless critical though, I'm not sure I'd want to go there as it could be dififcult to maintain and could slow down your workbook to a massive degree depending on how big it is.  I just offer it as a possible solution if there is no workaround any other way.

I'd definitely encourage you to contact the vendor though, to see if this is expected before or not.

I understand why you are stating the vendor would be a good person to contact and follow up with for some issues.

One thing is that you keep mentioning an addin.  Futuresource is not an addin, it is a stand alone application, and there is not an add in for Excel we are using.  We are just using base Excel with no bells and whistles.  The links from Futuresource are DDE Links that pull the data over.

All macros are turned off with notification, there are no add ins installed. 

Comments are used in the workbook so the workaround you mentioned is not an option. 

So if there are not any other ideas it may be ticket time. 

• Edited by Mike Lively Thursday, September 29, 2011 5:46 PM

The formula in the cell is =FSDDE|Quote!'fld:"XPH U1"[LABEL].LAST'

Where LABEL is a formula/value from the 3rd party application.

Based on this, there has to be some kind of addin/hook to Excel in order to pull the data out of your 3rd party application.  If it wasn't there, then Excel wouldn't recognize the formula and would return a #NAME? error.

Try this:

• Go to File-->Options-->Addins
• On that screen, choose COM Add-ins from the Manage dropdown.

This is generally the area where 3rd party apps install their add-ins (they are written in COM, rather than VBA, and are distributed and installed with the application rather than separately.)  I'd be surprised if there wasn't anything there.  (I've been wrong before though!)

I'm afraid it may be ticket time, unfortunately.  I don't know what other help I can offer.  I'd be curious to hear what the vendor has to say though.  :)

I will let you know what I find out, I am going to try to open the ticket in the morning.

Since this works fine on 07 with a computer without Futuresource installed, I am pretty sure it isn't the plug in.

Well we didn't open a  case as we think we found the issue before hand.

The first time a file is opened with a new verison of excel it has to update.  We found a support article mentioned this being the case since 2003.  We did confirm that after we saved the file once with 2010, the data did not try to update without us prompting it to do so.

 

 

• Marked as answer by Rex ZhangModerator Sunday, October 09, 2011 1:16 AM

Interesting, well found!  I wasn't aware of that, but it does make sense.

Solution :

 

Don't ask to update links for this workbook, and let me control whether links are updated

 

1.       On the Data tab, in the Connections group, click Edit Links.

        2.       Click Startup Prompt.

3.       It has 2 possibilities :

1.       Click Don't display the alert and don’t update automatic links => the first and second dialogue boxes above will be killed in the future.

2.       Click Don't display the alert and update links. => Only the first dialogue box above will be killed in the future, if in this worksheet, one of the links doesn’t exist anymore.

 

       Please take your time to try the suggestions. If anything is unclear or if there is anything I can do for you, please feel free to let me know.

Not what I am running into, but still does not solve the issue if it was what I was running into.  That setting does not stick until the file is saved, which will kill all values that were unable to update.

The issue was just http://support.microsoft.com/kb/327006

If you don't want it to update until you tell it to you can create a macro or vba code to update it. This will allow you to control the update process more specifically.

Matt Demaine

Excel How to - My Free Learning

I've had a similar issue where someone sends me an excel file that contains links to their C drive.

When i open the file from email, all is ok, as soon as i enable edit, some (not all) of the formulas change to REF.

I have solved by changing the trust settings on my Excel.

Open Excel
Select   File,
Select   Options,
Select    Trust Center, press the grey button "Trust Center Settings"
Select    External Content
Select "Disable automatic update of Workbook Links"
Select OK

This worked for me.

I know this is an old post, but I recently have been running into this same problem, and the answer from Rex Zhang (Moderator) did not help me at all, neither did the IT Help department at my job. After months of tinkering on my own, I finally figured out the problem, so I'm posting it here in hopes that it will help others.

Excel was automatically "trusting" all files that are saved on the company's network drive, which meant it automatically updated any links to files on our shared drives. I fixed this by doing the following.

In Excel:

go to File

click on Options (new box opens)

click on Trust Center (located in lefthand panel)

click on Trust Center Settings... (button in the middle of the box; new box opens)

click on Trusted Locations (located in lefthand panel)

Uncheck "Allow Trusted Locations on my network" box (below the list of trusted file paths)

click Ok

click Ok

• Proposed as answer by Bien-Aime 14 hours 2 minutes ago