Vlookup with file name change daily

[sbrmmd]

Please can someone help me,

I have 2 files with the name of

Schedule Jan 16, 2018 Test.xlsx ​

​

​

and other is ​

​

​

Report 16012018.xlsx ​

​

​

I want to do Vlookup in the file 1 and to take the name of file 2 to get the information I required from the cell.

Please see my formula

VLOOKUP(L10,'G:\SS\Box\P Schedule\January 2018\[Report 16012018.xlsx]Sheet1'!$B:$Q,3,FALSE)

The reason is that each day the file name is change to next date. I want to put in the reference cell file name and do vlookup using the file name in that cell.

Please let me know if you required more information.

Syed​

 

[Scott T]

You will need to use indirect. Note indirect requires that the file be open, in your example Report 16012018 would have to be open or you will get a REF error.

where A1 has the file name, Report 16012018

[FONT=Helvetica][COLOR=#252c2f]VLOOKUP(L10,INDIRECT("'G:\SS\Box\P Schedule\January 2018\["&A1&".xlsx]Sheet1'!$B:$Q"),3,FALSE)[/COLOR][/FONT]

 

[sbrmmd]

You will need to use indirect. Note indirect requires that the file be open, in your example Report 16012018 would have to be open or you will get a REF error.

where A1 has the file name, Report 16012018

[FONT=Helvetica][COLOR=#252c2f]VLOOKUP(L10,INDIRECT("'G:\SS\Box\P Schedule\January 2018\["&A1&".xlsx]Sheet1'!$B:$Q"),3,FALSE)[/COLOR][/FONT]

Thank you for your reply,
but I am getting an error

#REF!​

<colgroup><col></colgroup><tbody>
</tbody>

I opened the file but still getting this error, any suggestions

 

[Scott T]

Make sure that the cell with the file name is the same as the filename, no extra spaces or anything.

 

[sbrmmd]

Make sure that the cell with the file name is the same as the filename, no extra spaces or anything.

Yes, I copy paste the name of the file, one thing, my file is in share folder, and the address of the file is G:\SS\Box\P Schedule\January 2018\[20180017.xlsx]sheet1, if my file name is 20180117.xlsx.

I try to put the file on my Desktop, and change the location in the formula, but still the same error.

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

<colgroup><col width="64" style="width:48pt" span="7"> </colgroup><tbody> </tbody>

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

<strike></strike>

 

[Scott T]

make sure you got everything need to reference the file inducing the first '.
This is what is inside the quotes in the indirect formula

[COLOR=#252C2F][FONT=Helvetica][I]'G:\SS\Box\P Schedule\January 2018\["&A1&".xlsx]Sheet1'!$B:$Q[/I][/FONT][/COLOR]