SOLVED

Referencing Dynamic External Worksheet Filename (VLOOKUP/INDIRECT)

New 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
best response confirmed by mariengo1011 (New Contributor)
Solution

@mariengo1011 

How about

 

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