I would like to embed a filename, including worksheet name and cell locations, in a cell and then refer to that cell in "vlookup." Is this doable?

Suppose I want to run vlookup on a file name that changes every day.   I need to manufacture the filename based on the date, which I can do.  Now I want to refer to that file's name in the vlookup funs.  Howe would I do this.
For example I want to execute =vlookup(p16, [xyz_20160202]sheet1!a1:d1000, 2, 0).   However the name of the file xyz_"datestring" is constructed and put in cell d2.   So for instance D2 = [xyz_20160203]sheet1!a1:d1000.

I want to be able to call vlookup as

=vlookup(p16, D2, 2, 0) ,.,, but I can't get it to work.   can you think of any other way to softcode the name of the file?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Professor JMicrosoft Excel ExpertCommented:
Put it inside indirect function like this

=vlookup(p16, indirect(D2), 2, 0)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
Here is general syntax to build a reference to a range in another workbook using code:  
"'[" & strWBName & "]" & sSheetname1 & "'!" & sRange

Open in new window

strWBName is the name of the workbook
sSheetname is the name of the sheet, ie: sheet1
sRange is the range, ie: a1:d1000
dma70Author Commented:
Please look at Test.xlsx to tell me why both ideas are not working.
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

Rob HensonFinance AnalystCommented:
INDIRECT will only work when the source file is open.
dma70Author Commented:
The source file is open - still does not work for me.

Note:   One of the sample files I enclosed, has a potential error if you use it after yesterday - it calculates the date of the file.   Need to hardcode it to match the second file to ...20160202.   No matter what,  when I have the filename in the VLOOKUP function explicitly I get it to work.  When I softcode the file name either using indirect or not it doesn't seem to work.  

If it can't work with softcoding any file name,  please let me know.  If you can create a simple example of how it might work for a simple file that would certainly help.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you can also use a function to return the filename
Rob HensonFinance AnalystCommented:
The softcoding of the filename has to be exactly right and needs to include the FilePath as well. Need to look out for spaces, square brackets and apostrophes in particular.
dma70Author Commented:
Thanks for all your help -- the details of filename syntax were ultra-important and the indirect made it easier.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.