Solved

Converted Excel 2007 spreadsheet does not update links

Posted on 2011-02-16
15
1,175 Views
Last Modified: 2012-05-11
Hello Experts,
I have 2 converted excel 2007 workbooks that are linked (Both are .xlsm).

They used to update automatically in Excel 2003 but now I get a #value in all the fields.

Trust center settings
I have added my trusted sources on the network
under external content: enabled all data connections and enabled automatic update

Under excel options-->advanced
I have checked update links to other documents

formulas are set to automatic


I have tried the following under edit links:
click "update values" - I get "error: source not found"
click "change source" - I browse for the .xlsm file and select it (on the network) I get "error: source not found"
click "open source" - excel crashes


The only way I have gotten links to update is to open the source file FIRST and then the file with the links.  If I open the other way around, excel crashes.

The linked formulas are vlookups  
=VLOOKUP(K1,'S:\Reports\Daily Flash\[Sls&DemandFlash2010.xlsm]RBT ShippedSales'!$A$9:$AG$54,3,FALSE)

I dont know what else to try
0
Comment
Question by:CMRG
  • 7
  • 6
15 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 34920859
Try this - open file, then source files.  Check your links and make sure they say that all are OPEN (if not, then you're linked to another source version somewhere else).  Then, saved your source files to another directory (make a temp directory), then finally save your working document in that directory.

Then open the working document, check links that they are in the temp directory, and refresh links.

let me know what happens.

Dave
0
 

Author Comment

by:CMRG
ID: 34921148
I followed your steps and they are now both in the same folder but I still have to open the source file first to get links to work. If not, I get the "source not found error".
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34921360
when you open the working file, and look at links, can you tell those source links are in the new temp directory?
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:CMRG
ID: 34925990
They were not at first so I changed source and picked the new location, saved and closed the spreadsheet. I reopened hit update values and got "source not found".
0
 

Author Comment

by:CMRG
ID: 34925996
I should have mentioned that I verified the new path before I hit update values.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34930009
are these files confidential?  Can you upload for testing?

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34930047
Try saving to a temp directory on your c: drive or desktop.  Ensure file path is as short as possible to see if that's what is causing it.

Let me see if I can dream up a macro to inform you what your workbook thinks its links are (without using link manager)... It might be a while before I get to it.

In the meantime, if you could upload (even if you have to redact some data) so I can test with it, that would be great!

Dave
0
 

Author Comment

by:CMRG
ID: 34945494
I can't upload the file.

I did find an addin that will tell you where are the links are in your workbook.
http://www.accountingadvisors.com/exceldownloads.php

I gave it a try and it gave me a list of my links. The path is correct. I did notice that some of the links are using named ranges so I changed those to reference the cell range just to rule out that the named ranges were causing the problem. Unfortunately that didnt help. I will attach the list of links in case I am missing something.
links.xlsx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 34946710
Could it be the "&" sign in the file name that's causing the problem?

Is there a purpose to the YELLOW shading?  If these are where the error is being caused, try updating the vlookup references using [ ] brackets.

E.g.,

[E2] in your spreadsheet shows a vlookup like this and making a mockup seems to work

=VLOOKUP(K1,'S:\Reports\Daily Flash\Sls&DemandFlash2010.xlsm'!RBTShippedSalesData,5,FALSE)

However, change it to this and see what happens:

=VLOOKUP(K1,'S:\Reports\Daily Flash\[Sls&DemandFlash2010.xlsm]'!RBTShippedSalesData,5,FALSE)

Another thing to try - copy a few of these offending Vlookups in Yellow (if that's what they are - conjecture at this point) to another new workbook.  Create a dummy K1 and then enter a Vlookup.

Save the files, close them, open your new file and update links.  If that fails, then you have an isolated case that we can continue to work - and perhaps you can dummy up and upload (if the above first suggestions don't help.

Hang in there, we'll sort this out!

Dave
0
 

Author Comment

by:CMRG
ID: 34982747
Sorry, it took a couple days to get back to this.

I updated all the references using [] and that didnt help.

The "&" sign doesnt seem to be the issue either.

I created a new 2007 spreadsheet and created identical links to Sls&DemandFlash2010.xlsm. I saved closed and reopened the sheet. My links are working correctly.

Could it be a problem with the conversion from 2003-2007?
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 34983198
This is curious.

From scratch.  Go to your Excel 2003 file, ensure all links work correctly.  Save as 2007 file, close and open - still not working?

Then I would try this:

Open Excel 2003 file

Open a new workbook

Copy all the sheets to the new workbook

close the Excel 2003 file

Edit links to make sure none point to the Excel 2003 file, if they do, save your 2007 file, then change those links to the Excel 2007 file

Close and Open the 2007 file.  Do the links work now?


Dave
0
 

Author Comment

by:CMRG
ID: 35165190
Well, I have tried everything and could not fix the links. I am going to rebuild the spreadsheet and hopefully that will fix the problem for good. Thanks for trying. I appreciate your help.
0
 

Author Closing Comment

by:CMRG
ID: 35165203
This was a hard one. Thanks for the effort.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question