Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1207
  • Last Modified:

Converted Excel 2007 spreadsheet does not update links

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
CMRG
Asked:
CMRG
  • 7
  • 6
1 Solution
 
dlmilleCommented:
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
 
CMRGAuthor Commented:
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
 
dlmilleCommented:
when you open the working file, and look at links, can you tell those source links are in the new temp directory?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
CMRGAuthor Commented:
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
 
CMRGAuthor Commented:
I should have mentioned that I verified the new path before I hit update values.
0
 
dlmilleCommented:
are these files confidential?  Can you upload for testing?

Dave
0
 
dlmilleCommented:
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
 
CMRGAuthor Commented:
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
 
dlmilleCommented:
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
 
CMRGAuthor Commented:
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
 
dlmilleCommented:
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
 
CMRGAuthor Commented:
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
 
CMRGAuthor Commented:
This was a hard one. Thanks for the effort.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now