Vlookup and insert date from cell into file pathname

[adrienneb]

I have a Vlookup formula that works, but now I want to have it read a date from a cell in the current worksheet, and make that date/string? part of the full path filename telling Vlookup where to look (in another workbook).
The date is in cell B1 and I have named B1 "Date". It will never be today's date, it will always be a previous date. The filename is (using yesterday's date as an example)

Summary Report_20100309.xlsx

This is what I have:

=VLOOKUP(M4,'[C:\2021\FILES\03\[Summary Report_&TEXT($Date)&.xlsx]Summary]'!$A:$D,4,0)

How do I get it to recognize the string in B1 and make it part of the filename? Thank you!

[etaf]

indirect() should do it, thats how i created a similar path in the past
Not on a windows PC right now - on a MAC and not played with paths in a while
=VLOOKUP(M4,INDIRECT("'[C:\2021\FILES\03\[Summary Report_"&B1&"'!$A:$D"),4,0)
But that does fix all the path , and not just add the date part
BUT you could split the path out to different cells if you wanted to change say the folder 2021 to 2022 etc

[adrienneb]

Thanks... that did not work. I discovered that a full path name is not recognized; the file must be open. I played with it some more. The static filename+Sheetname is:
Trips Summary Report_2021MMDD.xlsx]Summary
I can get a formula to generate the variable filename by itself: =CONCATENATE("[Trips Summary Report_",INDIRECT("B1"),".xlsx]SUMMARY")
(The variable date is in Cell B1)

But inserting it into the VLOOKUP has me stumped:
original VLOOKUP formula: =VLOOKUP(L4,'[Trips Summary Report_2021MMDD.xlsx]Summary'!$A:$D,4,0)
I imagine it should be something like:
=VLOOKUP(L4,'(CONCATENATE("[Trips Summary Report_",INDIRECT("B1"),".xlsx]SUMMARY")'!$A:$D,4,0)
but that does not work.
Thanks for any help!!

[etaf]

perhaps a sample spread sheet attached with expected results
does
i assume the vlookup works if all hard coded
what does the look like - full path including name and range
and what bits are in a seperate cell

[adrienneb]

B1 is the variable date I want to be appended to Trips Summary Report_ ......
M4 has the static Vlookup formula, which works.

[etaf]

so this does NOT work
=VLOOKUP(L4,INDIRECT("'P:/Data Analysts/Commercial/2021/GEOTAB FILES/03/[Trips Summary Report_"&B1&".xlsx]Summary'!$A:$D"),4,0)

[adrienneb]

No. I also tried taking the full path away; and that did not work, either. Both times, the summary report was open.

=VLOOKUP(L4,INDIRECT("'[Trips Summary Report_"&B1&".xlsx]Summary'!$A:$D"),4,0)

[Limor_OP]

What is exactly the file name and the tab name?
could it be the value is not found in the target file?

[adrienneb]

Trips Summary Report_20210317.xlsx]Summary is there. When I hard-code it without trying to create it with the date in Cell B1, the formula executes just fine.

[Limor_OP]

what if you try it like that? just for the test:
=VLOOKUP(L4,INDIRECT("'[Trips Summary Report_"&"20210317"&".xlsx]Summary'!$A:$D"),4,0)

[adrienneb]

That works. =VLOOKUP(L4,INDIRECT("'[Trips Summary Report_"&"20210317"&".xlsx]Summary'!$A:$D"),4,0)
Decided to go one step further and tried:
=VLOOKUP(L4,INDIRECT("'[Trips Summary Report_"&$B$1&".xlsx]Summary'!$A:$D"),4,0)
and it works!!
Thank you Belinda!!

[Limor_OP]

Wow that's great
You're welcome

[etaf]

EDIT
Looks like while i was setting up examples and test
you now have an answer

Excel is able to use vlookup from closed spreadsheet - using a PATHNAME
https://www.excelforum.com/excel-for...-workbook.html
BUT indirect() needs the workbook to be open and wont work on closed workbooks
https://www.excelforum.com/excel-for...-function.html

Sorry I forget that in the past i used indirect.ext but that add-in was used with old version of excel 2010, i think i used on 2013 before
But Not used recently
Sorry for any confusion, you may need a VBA solution