Solved

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

Posted on 2014-12-16
3
61 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
3 Comments
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 400 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 32

Assisted Solution

by:Rob Henson
Rob Henson earned 100 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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

774 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