CMRG
asked on
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\Da
I dont know what else to try
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\Da
I dont know what else to try
ASKER
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".
when you open the working file, and look at links, can you tell those source links are in the new temp directory?
ASKER
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".
ASKER
I should have mentioned that I verified the new path before I hit update values.
are these files confidential? Can you upload for testing?
Dave
Dave
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
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
ASKER
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
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
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\Da
However, change it to this and see what happens:
=VLOOKUP(K1,'S:\Reports\Da
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
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\Da
However, change it to this and see what happens:
=VLOOKUP(K1,'S:\Reports\Da
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
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
This was a hard one. Thanks for the effort.
Then open the working document, check links that they are in the temp directory, and refresh links.
let me know what happens.
Dave