autopopulate part of VLOOKUP formula with data in another cell

[kudell]

Hello,
In working in Excel 2010.
I have this VLOOKUP function: =VLOOKUP(A2,'C:\Users\Kim Udell\...\[Quality-Review-XXX..X-System-Test-Plan.xlsx]Peer Review Summary Form'!$C$14:$F$22,4,False)

where the XXX..X represents a specific file name, which is different for each file. The rest of the file name is the same across all files concerned.

What I would like to do is put this information (the different part of the file name) into a cell on the same worksheet as the formula, and automatically populate this portion of the formula when the data is put into the separate cell.

HELP PLEASE ?!?!

[Special-K]

You need to concatenate part of the VLOOKUP with a cell reference
Something like

Enter the filename in A1
and change the formula to

=VLOOKUP(A2,'C:\Users\Kim Udell\...\[Quality-Review-"&A1&"-System-Test-Plan.xlsx]Peer Review Summary Form'!$C$14:$F$22,4,False)

[kudell]

Changed formula to: =VLOOKUP(A2,'C:\Users\Kim Udell\...\[Quality-Review-"&A1&"-System-Test-Plan.xlsx]Peer Review Summary Form'!$C$14:$F$22,4,FALSE)

and it gave me #N/A.
made sure that the information in A1 was formatted as text, still got #N/A.
reformatted A1 as General, still got #N/A.

Is there something else I'm missing??

[Jonmo1]

You're not going to be able to do this with any built in functions.

Normally, you can use Indirect to build a reference with a text string.
Like =VLOOKUP(A1,INDIRECT(A1&"!A1:B100"),2,FALSE)
Where A1 is a name of a sheet, say "Sheet1"
That would translate to
=VLOOKUP(A1,Sheet1!A1:B100,2,FALSE)

But Indirect doesn't work over closed workbooks.

There is an Add-In Called MoreFunc that has a similar indirect function that does work over closed workbooks.
Just do a google search for MoreFunc.
You'll have to sort through a whole bunch of ads and stuff to actually get to it.

[Tony Valko]

Like this...

A2 = XXX..X

=VLOOKUP(A2,INDIRECT("'C:\Users\Kim Udell\...\[Quality-Review-"&A2&"-System-Test-Plan.xlsx]Peer Review Summary Form'!C14:F22"),4,0)

However, this requires that the source file MUST be open. This is usually not desirable, especially if you're linking to many files.

[kudell]

Currently researching Morefunc as I am using many files...will check back in when I get something, but it seems that Morefunc may not work with Excel 2010....

Thank you all for your responses. If anyone else has any other suggestions, please let me know.