Referencing Dynamic External Worksheet Filename (VLOOKUP/INDIRECT)

%3CLINGO-SUB%20id%3D%22lingo-sub-2905670%22%20slang%3D%22en-US%22%3EReferencing%20Dynamic%20External%20Worksheet%20Filename%20(VLOOKUP%2FINDIRECT)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2905670%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20currently%20using%20VLOOKUP%20to%20extract%20multiple%20details%20from%20my%20tracker%2C%20where%20the%20filename%20changes%20as%20I%20save%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EScenario%3A%3C%2FP%3E%3CP%3EFile%20name%20yesterday%20%3A%20Tracker%20%3CSTRONG%3EOct-31%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EFile%20name%20today%20%3A%20Tracker%20%3CSTRONG%3ENov-01%26nbsp%3B%3C%2FSTRONG%3E(Which%20means%20excel%20file%20is%20replaced)%3C%2FP%3E%3CP%3EHere%20is%20an%20example%20of%20the%20Tracker%20file.%20The%20file%20name%20is%20updated%20every%20time.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mariengo1011_1-1635689355848.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321944i3FA89D6DA119E7DF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22mariengo1011_1-1635689355848.png%22%20alt%3D%22mariengo1011_1-1635689355848.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20my%20current%20formula%2C%20but%20the%20dilemma%20is%26nbsp%3B%3C%2FP%3E%3CP%3E%3DVLOOKUP(C5%2CINDIRECT(%22'%5BTracker%20%22%20%26amp%3B%3CFONT%20color%3D%22%23FFFF00%22%3E%26nbsp%3B%3CFONT%20color%3D%22%23FF0000%22%3E%3CREFERENCE%20cell%3D%22%22%3E%26nbsp%3B%3C%2FREFERENCE%3E%3C%2FFONT%3E%3C%2FFONT%3E%3CFONT%20color%3D%22%23FF0000%22%3E%26nbsp%3B%3C%2FFONT%3E%26amp%3B%20%22.xlsx%5DOLT'!%24A%3A%24B%22)%2C2%2CFALSE)%3C%2FP%3E%3CP%3EThe%20current%20worksheet%20that%20I%20am%20working%20on%20does%20not%20contain%20the%20'date'%20(Oct-31%20or%20Nov-01).%20It%20only%20contains%20column%20A%20(Place).%20Is%20there%20another%20workaround%20for%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20it%20be%20possible%20to%20reference%20an%20external%20workbook%20filename%20with%20regex%3F%20For%20example%2C%20any%20texts%20with%20Tracker%20%3CDATE%3E%3C%2FDATE%3E%3C%2FP%3E%3CP%3ERegex%20code%3A%20%3CSTRONG%3ETracker%5Cs%2B%5Cw%2B%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2905670%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2905711%22%20slang%3D%22en-US%22%3ERe%3A%20Referencing%20Dynamic%20External%20Worksheet%20Filename%20(VLOOKUP%2FINDIRECT)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2905711%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1158538%22%20target%3D%22_blank%22%3E%40mariengo1011%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20about%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DVLOOKUP(A2%2CINDIRECT(%22'%5BTracker%20%22%26amp%3BTEXT(TODAY()%2C%22mmm-dd%22)%26amp%3B%22.xlsx%5DOLT'!%24A%3A%24C%22)%2C3%2CFALSE)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

I am currently using VLOOKUP to extract multiple details from my tracker, where the filename changes as I save it. 


Scenario:

File name yesterday : Tracker Oct-31

File name today : Tracker Nov-01 (Which means excel file is replaced)

Here is an example of the Tracker file. The file name is updated every time. 

mariengo1011_1-1635689355848.png

 

Here is my current formula, but the dilemma is 

=VLOOKUP(A2,INDIRECT("'[Tracker " & <reference cell>  & ".xlsx]OLT'!$A:$C"),3,FALSE)

mariengo1011_0-1635690217640.png

The current worksheet that I am working on does not contain the 'date' (Oct-31 or Nov-01). It only contains column A (Place). Is there another workaround for this?

 

Would it be possible to reference an external workbook filename with regex? For example, any texts with Tracker <Date>

Regex code: Tracker\s+\w+ 

 

Thank you!

 

1 Reply

@mariengo1011 

How about

 

=VLOOKUP(A2,INDIRECT("'[Tracker "&TEXT(TODAY(),"mmm-dd")&".xlsx]OLT'!$A:$C"),3,FALSE)