Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 76
  • Last Modified:

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
0
WeThotUWasAToad
Asked:
WeThotUWasAToad
2 Solutions
 
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 HensonIT & Database AssistantCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now