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

[rcm]

Dear Syed:

Be so kind to provide sample workbooks so we can help you better and faster.

[FDibbins]

To reference a cell to give you the file name for use in a formula, you need to use INDIRECT. However, that only works if the source file is open.
If you cannot always have the source file open, take a look at using the Morefunc add-in, it allows using INDIRECT on closed workbooks
http://www.ashishmathur.com/tag/morefunc/

[Sbrmmd]

Schedule Jan 17, 2018 with 4 RT Test.xlsx20180116.xlsx

Please see the attached files, I want to put values in file "20180117" and it can show in real time in "Schedule Jan 17, 2018 with 4 RT Test".

Please let me know if you need more information. Thanks