variable path name based on cell value

[Geordie]

Hi All

I want to enter the following formula

=VLOOKUP(D3,'[172-sep-14.xlsx]Sheet1'!$A$4:$C$34,3,FALSE)

with 172-sep-14 being a file name

is it possible to have all or part of the file name as a variable

Example

enter a number 172 into cell A3 and for that number to be used in the formula

=VLOOKUP(D3,'[A3-sep-14.xlsx]Sheet1'!$A$4:$C$34,3,FALSE) somehow for the 172 to be dragged from cell A3

I hope this makes sense?

[hoyasaxa215]

You can fairly easily accomplish this through using the =INDIRECT function (link below). You can create an =INDIRECT function that references a cell where you can input/update/change the name of the file that you want to reference.

http://office.microsoft.com/en-us/ex...in=HP010342656

[Geordie]

Thanks

Sorry but I still have no idea how to use the indirect function

[hoyasaxa215]

Here's a template for using a dropdown lookup that relies on the =INDIRECT function. This should help.

[Geordie]

Thanks for the example workbook

Not sure if I have explained things well enough

I need the following

I am entering a lookup formula inyo one workbook and for it to lookup the value in separate workbook which will be closed

The file name of the closed workbook is the value in A3 plus -Sep-14.xlsm
for example if A3 = 323 then the file name will be
323-Sep-14.xlsm

I hope this makes it clearer

Thanks in advance