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?

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

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

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

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

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