Contatenate folder path strings, how ?

[Excel-Guy]

This is what I have now:

='C:\Reports\[MyReport.xls]March'!B4

I would like to have the path in one central place, instead of updating each
cell if the workbook is moved, renamed or splitted in more workbooks in
different folders.

This way when the Workbook is moved I only have to update the path in one
location. e.g. (cell A2 on AnotherTab)

In cell A2 on AnotherTab I store the path: C:\Reports\[MyReport.xls]

And construct something like this to make it work:

=AnotherTab!A2&'March'!B4 (<-- this does not work)


Can anyone help me please?

 

[Per Jessen]

You need the INDIRECT function:

=INDIRECT(AnotherTab!A2&"March!B4")

Regards,
Per

 

[Jacob Skaria]

There is a way to get this using INDIRECT() as below but this works only if
the file is open.

=INDIRECT("'" & AnotherTab!A2 & "March'!B4")

There is no built in function which can do this. You can try the add-in
called Morefunc which has a function called INDIRECT.EXT that will work even
if the source book is closed..

Check out
http://xcell05.free.fr/morefunc/english/index.htm