Solved

Converted Excel 2007 spreadsheet does not update links

Posted on 2011-02-16
15
1,167 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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
when you open the working file, and look at links, can you tell those source links are in the new temp directory?
0
 

Author Comment

by:CMRG
Comment Utility
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
Comment Utility
I should have mentioned that I verified the new path before I hit update values.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
are these files confidential?  Can you upload for testing?

Dave
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
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 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
This was a hard one. Thanks for the effort.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now