Changing folder/file name in formula

[Gallinski]

HI all.

Is it possible to change the folder/file name in a formula.

IE. in cell A1 for instance I have a client name - BOB. In cell A2 i would like a formula that opens file path C:\clients\bob\bob.xls and give the A1 refence in that file.

However the A1 cell with BOB will change names to JOE, BILL, MATT etc. hence the formula needs to change folder/file names as well.

Is this possible?

Thanks in advance.

[humdingaling]

yes
=HYPERLINK("C:\clients\"& A1 & "\"&A1&".xlsx")

Also
if you dont want the whole path listed and only the name
=HYPERLINK("C:\clients\"& A1 & "\"&A1&".xlsx",A1)

[Gallinski]

Ok, seems to work. Thanks.
What if I need it from a different tab/sheet name within that file. IE Sheet/tab names are Bob March, Bob April etc.

[Gallinski]

Sorry to confuse you all further, but I think I might be off track here.

In the end I need something like the following.

cell A1 - client name
cell A2 - =large(c:\clients\bob\bob.xlsx\totals tab!$d$5:$d$10,1)

The client name in A1 will always change, therefore I need the client in the formula to change with it.
The totals tab and cell references are the same location in every client file, so its only the client name that changes.

Thanks again

[humdingaling]

are you linking the file itself or information from the file?

[humdingaling]

=LARGE(INDIRECT("["&A1&".xlsx]"&B1&"!$D$5:$d$10"),1)
A1 = File name
B1 = Sheet name

if you try to put the path in it doesnt work...i dont know how to get around it

[Gallinski]

Thanks for all your help in this Humdingaling.

I've got this at the moment, but due to the limits of INDIRECT, only works if target file is open.

=LARGE(INDIRECT("'c:\clients\"&$A$1&"\["&$A$1&" .xls]April'!$D$5:$D$10"),1)

*EDIT*
I will download INDIRECT.EXT shortly, and this should work fine.