?
Solved

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

Posted on 2014-12-16
3
Medium Priority
?
70 Views
Last Modified: 2015-01-01
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
Comment
Question by:WeThotUWasAToad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 1600 total points
ID: 40504221
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
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 400 total points
ID: 40504396
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
 

Author Closing Comment

by:WeThotUWasAToad
ID: 40526873
Thanks for the responses.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question