Find/Replace Linked Cells Prompts File Location
 

In previous versions of Excel, I could easily manipulate links by using
the copy/paste and find/replace functionality.

For example, I have the link
='[07-05ApprovedAPSourcingDecisionsWithSQData.xls]Charts by
CommData'!$B15 in one cell and the link
='[08-05ApprovedAPSourcingDecisionsWithSQData.xls]Charts by
CommData'!$B15 in the one below. As you can see, the link is changing
the file name with the reference to the same cell in each file (I have
a file for each of the 12 months). I copy and paste that to the next
column and it prompts me for the file location of each cell!!! I
thought that maybe I need to open the files, but it still prompts me.
Once I go through all of the locations to get a new column of links, I
then need to change the $B15 reference to $B16. I tried to use a
simple find/replace and I am stuck selecting the file locations for
each change AGAIN. This is NOT efficient at all!

Is there a setting that can fix this? I just recently upgraded to
Office 2003, so I may be ignorant on this...

Any suggestions are greatly appreciated!!

I notice that your link formula as shown does not include the path.
That is the form that the formula would have when the source file is
open. If you prepend the path to the workbook it should work when the
source file is closed:
='C:\MyDir\[07-05ApprovedAPSourcingDecisionsWithSQData.xls]Charts by
CommData'!$B15

Does that help?

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

No. That did not work. This is so frustrating? I cannot believe that
a newer version of Excel would complicate work.

I have the entire path
='C:\Metrics\Sourcing\APSourcing\Distribution\2005 \MonthlySourcing\[08-05ApprovedAPSourcingDecisionsWithSQData.xls]Charts
by CommData'!$B$8 and if I copy/paste it I am prompted to select the
location of the file by a popup window.

Any other suggestions??

Thanks!

Inquisitive1 wrote:
='C:\Metrics\Sourcing\APSourcing\Distribution\2005 \MonthlySourcing\
[08-05ApprovedAPSourcingDecisionsWithSQData.xls]Charts by CommData'!$B$8

This path is rather long (130 characters).
I wonder if that is somehow the root of the problem.

Try an experiment with the source file in C:\Temp and see if the problem persists

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Find/Replace Linked Cells Prompts File Location
 

No luck with shorter path names. Has nobody else experienced this
problem? Maybe I have a corrupt file??