Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Renaming a Source Link
I have several workbooks which are source links to a master sheet. I rename
these workbooks each week after I am finished with the week's data. (I "save as")My problem is that my links automatically change to link to the newly named workbook. I do not want this. I want the original workbook to always remain the source of the links. Can someone help me with this problem. Also, while I am asking ... why do these links #REF! sometimes when I try to update? How can I prevent that from happening? SImilarly, I sometimes get a #VALUE! error when I want to open up my master sheet, but not update the links? How can I prevent this as well? |
#2
|
|||
|
|||
One option would be to close your master sheet before saving the source
sheets with different names. Another option would be to do the saving using a macro using SaveCopyAs rather than SaveAs. A third option would be to close the source workbooks and just copy the files to the different locations using Windows explorer (or FileCopy in a macro). You haven't said which version of Excel you are using (it always helps if you do). For errors appearing when you don't update links see: http://support.microsoft.com/default...b;en-us;327006 Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
|
|||
|
|||
Sorry I am using Excel 2002.
Well I suppose the simplest answer to the first question is to make sure my master sheet has been closed before renaming the other sheets. As for my last 2 questions about the #VALUE! and #REF! ... Is there something besides ensuring all my sheets are open at once to prevent this? It becomes cumbersome having to open 20 worksheets at the same time when I am not neccessarily needing to work in them. And once a formula has #REF! I've never been able to get it to work again unless I repaste my link... which is a real pain in the butt. "Bill Manville" wrote: One option would be to close your master sheet before saving the source sheets with different names. Another option would be to do the saving using a macro using SaveCopyAs rather than SaveAs. A third option would be to close the source workbooks and just copy the files to the different locations using Windows explorer (or FileCopy in a macro). You haven't said which version of Excel you are using (it always helps if you do). For errors appearing when you don't update links see: http://support.microsoft.com/default...b;en-us;327006 Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#4
|
|||
|
|||
I did not notice the link at the bottom of your answer until just now, but I
have reviewed the site, which states that these problems could exist when working with sheets that are not all from the same version of excel. In my case, this is not so, all my linked sheets are saved as Excel 2002 and are all found on the same office server and only updated and changed by me. Any other ideas? "Arla M" wrote: Sorry I am using Excel 2002. Well I suppose the simplest answer to the first question is to make sure my master sheet has been closed before renaming the other sheets. As for my last 2 questions about the #VALUE! and #REF! ... Is there something besides ensuring all my sheets are open at once to prevent this? It becomes cumbersome having to open 20 worksheets at the same time when I am not neccessarily needing to work in them. And once a formula has #REF! I've never been able to get it to work again unless I repaste my link... which is a real pain in the butt. "Bill Manville" wrote: One option would be to close your master sheet before saving the source sheets with different names. Another option would be to do the saving using a macro using SaveCopyAs rather than SaveAs. A third option would be to close the source workbooks and just copy the files to the different locations using Windows explorer (or FileCopy in a macro). You haven't said which version of Excel you are using (it always helps if you do). For errors appearing when you don't update links see: http://support.microsoft.com/default...b;en-us;327006 Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#5
|
|||
|
|||
Use Bill's last suggestion. There is no way your links will change if once
ALL the files are closed, you use Explorer to copy them to a different location. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Arla M" wrote in message ... I did not notice the link at the bottom of your answer until just now, but I have reviewed the site, which states that these problems could exist when working with sheets that are not all from the same version of excel. In my case, this is not so, all my linked sheets are saved as Excel 2002 and are all found on the same office server and only updated and changed by me. Any other ideas? "Arla M" wrote: Sorry I am using Excel 2002. Well I suppose the simplest answer to the first question is to make sure my master sheet has been closed before renaming the other sheets. As for my last 2 questions about the #VALUE! and #REF! ... Is there something besides ensuring all my sheets are open at once to prevent this? It becomes cumbersome having to open 20 worksheets at the same time when I am not neccessarily needing to work in them. And once a formula has #REF! I've never been able to get it to work again unless I repaste my link... which is a real pain in the butt. "Bill Manville" wrote: One option would be to close your master sheet before saving the source sheets with different names. Another option would be to do the saving using a macro using SaveCopyAs rather than SaveAs. A third option would be to close the source workbooks and just copy the files to the different locations using Windows explorer (or FileCopy in a macro). You haven't said which version of Excel you are using (it always helps if you do). For errors appearing when you don't update links see: http://support.microsoft.com/default...b;en-us;327006 Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#6
|
|||
|
|||
No other ideas without more information...
Take an example cell in the master workbook which gives a #REF error. What do you see in the formula bar when you select that cell? What setting do you have in the master workbook for Tools / Options / Calculation / Save external link values? Opening the source workbook, what does the value in that cell change to? If you are referencing a single cell in the source workbook, what formula does it contain? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2003 update link when Source open | Excel Worksheet Functions | |||
Link - deactivate being sent to source when click on linked cell? | Excel Worksheet Functions | |||
How do I break a link to a source when opening Excel 2000? | Excel Worksheet Functions | |||
How do link to a remote worksheet using the path value in a field? | Links and Linking in Excel | |||
Unable ot Break link for Macro with no source | Excel Discussion (Misc queries) |