External workbook links

[brucemc]

Hi!

I have a zillion references in a workbook to roughly five other workbooks, and they all take the form of:
=+'U:\TLC\TLC\ARO\2010 Budget\PPHD\[Dept Bgt 2010 Sales.xls]Summary'!C17
Lord nly knows why my wife still insists on putting that stupid "+" at the start, but moving on... I want to wipe out the portion of all those links of:
+'U:\TLC\TLC\ARO\2010 Budget\PPHD\
and replace it with
'

so it will always be simply looking in the local directory for these workbooks, but every time I try using find and replace instead of finding and replacing, I get an idiot popup file selection window entitled "Update Values:<blah blah blah>". All I want is for it to do what I am telling it to do as the format I am trying to force on the darned workbook is correct according to documentation!!!

Any help?

 

[xenou]

My experience with links is that:
If the external workbooks are closed, you must use a full filepath.
If the external workbooks are open, you can use the file name by itself

It sounds like you want the second option, but without opening those workbooks. I don't believe that is possible.

 

[brucemc]

I thought that if they were in the same directory I could omit the path, but perhaps not... In any event, the dang thing won't let me obliterate path names from the reference!

 

[xenou]

Nope.
I have found if you open the linked workbooks the full paths will disappear, but if the external workbooks are closed Excel needs the full paths. I don't believe Excel supports relative paths so it needs the full path to find the workbook, but maybe when the workbook is open it can use a simplified algorithm to maintain the link.