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?

dma70
dma70 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Microsoft Excel Expert
Top Expert 2014
Commented:
Put it inside indirect function like this



=vlookup(p16, indirect(D2), 2, 0)
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
Here is general syntax to build a reference to a range in another workbook using code:  
"'[" & strWBName & "]" & sSheetname1 & "'!" & sRange

Open in new window

WHERE
strWBName is the name of the workbook
sSheetname is the name of the sheet, ie: sheet1
sRange is the range, ie: a1:d1000

Author

Commented:
Please look at Test.xlsx to tell me why both ideas are not working.
CMA_DV_Data_Sheet-v1_20160202.xls
test.xlsx
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Rob HensonFinance Analyst

Commented:
INDIRECT will only work when the source file is open.

Author

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 Programming
Top Expert 2015

Commented:
you can also use a function to return the filename
Rob HensonFinance Analyst

Commented:
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.

Author

Commented:
Thanks for all your help -- the details of filename syntax were ultra-important and the indirect made it easier.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial