Variable filename in vlookup

**Lizzietish11** · 06-28-2010, 09:20 AM

I have a spreadsheet file that is processed by my system every monody. I have another reporting tool to edit down the info however since this is populated weekly I have different versions each week. Weekly report - week 1, weekly report - week 2, and so on. in the reporting tool I enter "week 1" into cell "B1" but in my vlookup formulas I have to edit the file name path C:\recaps\weeklyreport - week1 with a find and replace each time I do it. Is there anyway I can edit my lookup formula to pull from a variable workbook and when I update cell “B1” the formula knows to pull from that workbook

**werfnort** · 06-28-2010, 04:07 PM

If everything else is staying the same except for the file name, you might be able to use INDIRECT and CONCATENATE to accomplish this.

Please Login or Register  to view this content.

Look at the way the formula is constructed when you set up the vlookup normally.

It should be something like 'Week1.xls'!$A$1:$B$100

So with concatenate, you just need to create the rest of it as text, except for the part that will change. I added spaces below to make it a little more clear.

Please Login or Register  to view this content.

**Lizzietish11** · 06-28-2010, 04:09 PM

worked perfectly thanks!

**ats1312** · 05-02-2013, 05:47 AM

hi, i have also tried this solution but i get a #REF! when the spreadsheet is closed?

It works fine when the sheet is open?

Any ideas?

Thanks

=VLOOKUP("AP Total",INDIRECT(CONCATENATE("'K:\Finance\2013\AP\Workbooks\[Mar13 - ",LEFT(D111,4),"-",RIGHT(D111,4),".xlsx]Summary'!$A:$U")),3,FALSE)

**arlu1201** · 05-02-2013, 05:58 AM

Ats,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

**silverback_yote** · 03-19-2016, 08:30 AM

Hi guys,

I have the same problem. This is my vlookup formula.
=VLOOKUP(B6,'C:\Users\Downloads\[150826.xlsx]Sheet1'!$B$1:$C$250,2,FALSE)

I want "150825" would be variable.

This is what I did
=VLOOKUP(B6,INDIRECT(CONCATENATE("C:\Users\Downloads\[",D3,".xls]Sheet1'!$B$1:$C$250")),2,FALSE)
150826 is located in D3
However, it is not working. #REF! is the output.

An advise is much appreciated. Thanks.