Retrieving Data from another workbook?

[SPH]

Is there a way to have a cell in workbook 1 automatically retrieve a value from workbook 2? Not only retrieve it, but be able to change the directory of the different workbook 2 without changing the actual formula in the cell of workbook 1

This is the formula for getting data from another workbook
='C:\mydata\[Data.xls]Data'!$A$

I just cant find a way to change the directory 'Mydata' without actually manually changing the formul
There must be a way...
Thanks

 

[Harlan Grove]

...
...

This is the formula for getting data from another workbook:
='C:\mydata\[Data.xls]Data'!$A$1

I just cant find a way to change the directory 'Mydata' without actually
manually changing the formula

...

Since you can't have multiple workbooks with the same base filename open at the
same time, it's likely your these workbooks wouldn't be open. That being the
case consider the alternatives in

http://www.google.com/[email protected]