Solved

Converted Excel 2007 spreadsheet does not update links

Posted on 2011-02-16
15
1,177 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

679 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