Dynamic file path

[chamullerousa]

I have a question about how to do something (or if it is even possible to do) in excel 2003.

I have a single file that references various other files.

A1=John Doe
A2=='C:\My Documents\School\John Doe\[John Doe.xls]Sheet 1'!$H$5

A2 is not referencing A1 right now but I would like it to so that what I type in A1 will change the file path in A2.
For example:

if A1=John Doe
then A2=='C:\My Documents\School\John Doe\[John Doe.xls]Sheet 1'!$H$5
and
if A1=Jane Smith
then A2=='C:\My Documents\School\Jane Smith\[Jane Smith.xls]Sheet 1'!$H$5

I am trying to figure out how John Doe in A2 can be replaced with a reference to A1.

Currently I have to use Ctrl+H to Find and Replace the names over and over. Seeing as how this is needed 12 times per sheet on 24 sheets, even macros can't reduce the tediousness of updating regularly.

Please help!

 

[texasalynn]

Welcome to MrExcel Board...

here is a literal of that

Excel Workbook
	A	B
1	Jane Smith	'C:\My Documents\School\Jane Smith\[Jane Smith.xls]Sheet 1'!$H$5
Sheet1

but I'm sure that's not what you really wanted. The Indirect feature could do that but it only works with open workbooks.

 

[chamullerousa]

Kind of but I don't want the path to display the path with text, I want it to retrieve the data from the location but have the location adjust when the value in A1 is changed.


Cell Formula
B1 ='C:\My Documents\School\"&A1&"\["&A1&".xls]Sheet 1'!$H$5

 

[texasalynn]

not sure if that was working but I tested this and it worked.

Excel Workbook
	A	B
1	MrExcel	5
Sheet1

 

[chamullerousa]

I have to be honest. I copied exactly what you typed in and it isn't working for me. Seriously. I went through keystroke by keystroke. Are you using 2007? Could that be the issue?

workbook1 (MrExcel.xls)
A1=5

workbook2
A1=MrExcel
B2='C:\Documents and Settings\mjmarsha\Desktop\[&A1&.xls]Sheet1'!$A$1

B2 is still showing an error.

 

[chamullerousa]

ok so I went home and tried the same thing on 2007 and still no change. i am realizing that you are using 2003 by the .xls extension in your formula. i really appreciate your help. I am hoping someone out there can try and duplicate your success and explain to me what I may be doing wrong because it isn't work for me.

any more ideas?