Bookmark Special Paste, Excel look in same folder

Question

I have a word doc and an excel doc, I have created bookmarks in word and special pasted into excel.....These bookmarks works AS LONG as the files stay in the ORIGINAL location (example- C:\Documents and Settings\My Documents\New Form)

BUT

I need to move the documents to different client locations (example- C:\Documents and Settings\My Documents\client1)

In excel the cell information reads like this;

=Word.Document.8|'C:\Documents and Settings\My Documents\NewForm\TestFactFind.doc'!'!Title'

Is there anything I can put so the excel file will look into its current folder to find the word document instead of a set location?

Answer 1

NON VBA Method

Yes it is possible for the Excel formula to pick up the value from a file from the same directory where the formula containing files reside. You have to use INDIRECT(). However a word of caution. INDIRECT will work only if the destination workbook is open.

Here is an example

Let's say the formula in Cell B1 in C:\Book1.xlsx is

='C:\Sample\[Sample.xls]Sheet1'!$A$1

Then put this formula in Cell C1 for testing it

=INDIRECT("'" & LEFT(CELL("filename",A1),FIND("[",CELL("filename",$A$1))-1)&"[Sample.xls]Sheet1'!$A$1")

Screenshot