asked on

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?

ASKER CERTIFIED SOLUTION

Professor J

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

crystal (strive4peace)

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

dma70

ASKER

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

Rob Henson

INDIRECT will only work when the source file is open.

dma70

ASKER

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)

you can also use a function to return the filename

Rob Henson

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.

dma70

ASKER

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

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

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?