Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Arla M
 
Posts: n/a
Default 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   Report Post  
Bill Manville
 
Posts: n/a
Default

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   Report Post  
Arla M
 
Posts: n/a
Default

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   Report Post  
Arla M
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

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   Report Post  
Bill Manville
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
2003 update link when Source open tim Excel Worksheet Functions 3 January 17th 05 07:25 AM
Link - deactivate being sent to source when click on linked cell? snidely Excel Worksheet Functions 1 January 11th 05 03:11 AM
How do I break a link to a source when opening Excel 2000? GRACE Excel Worksheet Functions 1 January 7th 05 04:00 PM
How do link to a remote worksheet using the path value in a field? Michael T. Links and Linking in Excel 3 December 11th 04 09:45 AM
Unable ot Break link for Macro with no source nate_l Excel Discussion (Misc queries) 1 December 10th 04 01:42 AM


All times are GMT +1. The time now is 06:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"