Reference the most recent Excel workbook when multiple filenames in a single folder include a common prefix

Hello,

When writing a formula in Excel (2013) which references content from a different worksheet, is there a way to:

       a) use wildcards for part of a referenced filename?
       b) reference only the most recent file with a common filename prefix?

For example, suppose you have a folder which contains the following files:

C:\Users\Username\Documents\Data\
        Content_20141208.xlsx
        Content_20141209.xlsx
        Content_20141210.xlsx
        Content_20141211.xlsx
        Content_20141212.xlsx
        Content_20141215.xlsx
        Content_20141216.xlsx
        Report.xlsx

…and in which a new up-to-date "Content…" file is created each weekday from the previous day's file (by File > Save As...) with the last eight characters in its filename designating the date it was created.

Suppose also that the workbook, "Report.xlsx", contains the following formula:

        =VLOOKUP(A,'Content_20141216.xlsx'!InfoRange,5,0)

Is there a way to include a wildcard or modify the formula some other way, so that it always looks in the most up-to-date "Content_…" workbook?

Thanks
WeThotUWasAToadAsked:
Who is Participating?
 
Rgonzo1971Commented:
HI,

pls try this User defined function( insert this function in amodule of your Report File)

Function NewestFile(strPath As String) As String()
Result = ""
        myFile = Dir(strPath & "\Content_*.xlsx")
        Do While Len(myFile) <> 0
            If myFile > Result Then Result = myFile
            myFile = Dir
        Loop
NewestFile = Result
End Function

Open in new window


As parameter use C:\Users\Username\Documents\Data

Regards
0
 
Rob HensonFinance AnalystCommented:
You can use the ADDRESS function to create a filke name string and then use the INDIRECT function to refer to it.

However, INDIRECT needs the source file to be open.

Thanks
Rob H
0
 
WeThotUWasAToadAuthor Commented:
Thanks for the responses.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.