If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
A link between two workbooks doesn't keep the value, shows #va
Did you try changing the windows registry setting that Jim described in his
post? Lance wrote: Thanks for the information, but I do question it a little in that if I open the same spreadsheet in Excel 2003, all of the cells with =sumif() formulas in them contain valid data results as I would expect (even though I don't have access to the source spreadsheet in the formulas link). In 2007 however, I can only get those results if automatic calculation is turned off. "Dave Peterson" wrote: The =sumif() formula doesn't work in any version of excel if the sending workbook is closed. But the way the cells with links are displayed if the links aren't updated did change (I think with xl2k). Jim Rech posted a registry tweak: http://groups.google.com/groups?thre...GP11.phx .gbl You'll have to change the version number in Jim's post. Lance wrote: I guess my big question here is why did this appear to work in Excel 2003, but not now in Excel 2007? It used to be that users would elect not to update the links and then in the spreadsheet they received via e-mail, it would have the data still contained within the spreadsheet--though it was stale, at least it was there. Now in Outlook 2007, it appears as if the data is no longer available unless it can update the links OR unless I save the file locally, open excel by itself, turn calculation of formulas to manual, then open the spreadsheet from within Excel 2007. Any thoughts on this and how to get it to function as it did in Excel 2003? Much appreciated! Lance -- Dave Peterson -- Dave Peterson |
#12
|
|||
|
|||
A link between two workbooks doesn't keep the value, shows #va
Dave,
I actually did try the registry hack described by Jim and it did appear to work, but deploying that to the masses would be difficult. That being said, I opened up a case with Microsoft on this and the lady I spoke with said that the registry change can work, but they have also seen it cause issues with mis-calculating some formulas. She said that the reason it's doing what it is can be explained in that the spreadsheet with the links in it was created and saved in Excel 2003 and then when an Excel 2007 client opens it, it sees that it was created in a different version and immediately wants to update all of the links and calculations (even if you tell it not to). So unfortunately, this is by design, but breaking the link before sending is one option to get around the issue (along with the registry change and/or a copy and paste special). Also, I believe if the person who saves is is using Excel 2007, the issue will be gone for other Excel 2007 clients. Supporting document: 925893 External links may be updated in Excel 2007 when you open a workbook that was last saved in an earlier version of Excel http://support.microsoft.com/default...b;EN-US;925893 Lance "Dave Peterson" wrote: Did you try changing the windows registry setting that Jim described in his post? Lance wrote: Thanks for the information, but I do question it a little in that if I open the same spreadsheet in Excel 2003, all of the cells with =sumif() formulas in them contain valid data results as I would expect (even though I don't have access to the source spreadsheet in the formulas link). In 2007 however, I can only get those results if automatic calculation is turned off. "Dave Peterson" wrote: The =sumif() formula doesn't work in any version of excel if the sending workbook is closed. But the way the cells with links are displayed if the links aren't updated did change (I think with xl2k). Jim Rech posted a registry tweak: http://groups.google.com/groups?thre...GP11.phx .gbl You'll have to change the version number in Jim's post. Lance wrote: I guess my big question here is why did this appear to work in Excel 2003, but not now in Excel 2007? It used to be that users would elect not to update the links and then in the spreadsheet they received via e-mail, it would have the data still contained within the spreadsheet--though it was stale, at least it was there. Now in Outlook 2007, it appears as if the data is no longer available unless it can update the links OR unless I save the file locally, open excel by itself, turn calculation of formulas to manual, then open the spreadsheet from within Excel 2007. Any thoughts on this and how to get it to function as it did in Excel 2003? Much appreciated! Lance -- Dave Peterson -- Dave Peterson |
#13
|
|||
|
|||
AW: A link between two workbooks doesn't keep the value, shows #va
There's a free solution for getting updated data from CLOSED
XLS-FILES!!!!!!!!!!!! You can (like I did for my company) download freeware (virus-free!!!), which adds about 85 new functions to your excel. I had my problem fixed... URL: http://xcell05.free.fr/english/ Read it, & enjoy! Greetz, Patrick Lance: Dave, I actually did try the registry hack described by Jim and it did appear to work, but deploying that to the masses would be difficult. That being said, I opened up a case with Microsoft on this and the lady I spoke with said that the registry change can work, but they have also seen it cause issues with mis-calculating some formulas. She said that the reason it's doing what it is can be explained in that the spreadsheet with the links in it was created and saved in Excel 2003 and then when an Excel 2007 client opens it, it sees that it was created in a different version and immediately wants to update all of the links and calculations (even if you tell it not to). So unfortunately, this is by design, but breaking the link before sending is one option to get around the issue (along with the registry change and/or a copy and paste special). Also, I believe if the person who saves is is using Excel 2007, the issue will be gone for other Excel 2007 clients. Supporting document: 925893 External links may be updated in Excel 2007 when you open a workbook that was last saved in an earlier version of Excel http://support.microsoft.com/default...b;EN-US;925893 Lance "Dave Peterson" wrote: Did you try changing the windows registry setting that Jim described in his post? Lance wrote: Thanks for the information, but I do question it a little in that if I open the same spreadsheet in Excel 2003, all of the cells with =sumif() formulas in them contain valid data results as I would expect (even though I don't have access to the source spreadsheet in the formulas link). In 2007 however, I can only get those results if automatic calculation is turned off. "Dave Peterson" wrote: The =sumif() formula doesn't work in any version of excel if the sending workbook is closed. But the way the cells with links are displayed if the links aren't updated did change (I think with xl2k). Jim Rech posted a registry tweak: http://groups.google.com/groups?thre...GP11.phx .gbl You'll have to change the version number in Jim's post. Lance wrote: I guess my big question here is why did this appear to work in Excel 2003, but not now in Excel 2007? It used to be that users would elect not to update the links and then in the spreadsheet they received via e-mail, it would have the data still contained within the spreadsheet--though it was stale, at least it was there. Now in Outlook 2007, it appears as if the data is no longer available unless it can update the links OR unless I save the file locally, open excel by itself, turn calculation of formulas to manual, then open the spreadsheet from within Excel 2007. Any thoughts on this and how to get it to function as it did in Excel 2003? Much appreciated! Lance -- Dave Peterson -- Dave Peterson |
|
Thread Tools | |
Display Modes | |
|
|