Solved

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

Posted on 2014-12-16
3
58 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 48

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 31

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now