How do I use 3 cells to create the string for a lookup function?

[Guest]

I am trying to create a lookup string by joining information from two
different cells that give the name of the directory and the filename. For
example: one cell may have N:/ and the second cell has todaymicrosoft. I
have created a string in a new cell that joins the two cells and reads
N:/todaymicrosoft. I want to create a vlookup function that can read the new
cell with the N:/todaymicrosoft string as the lookup file within the vlookup
function =VLOOKUP(B2,[microsoft.xls]Sheet1!$A$4:$B$6,2). I can create the
string, but do not know how to have excel read the string as a filename when
I refer to it in the vlookup function..... Help!

 

[Biff]

Hi!

You can use the INDIRECT function to do this, however, it will require that
the other file be open or it will not work. Since the other file will need
to be open the path is not needed.

So, what's the name of the file, todaymicrosoft or just microsoft? I'll
assume it's microsoft.xls.

C2 = microsoft

=VLOOKUP(B2,INDIRECT("'["&C2&".xls]sheet1'!A4:B6"),2)

Biff