CODEWITHSUNDEEP
excelvba
OlaOkland
OlaOkland

Reputation: 95

Cell reference for workbook that changes name every month

I have a cell D5 that is linked to another workbook with the formula [January.Book1.xlsx]Sheet1!$D$5.

The problem is that the January.Book1.xlsx is called February.Book1.xlsx with updated numbers the next month. However , Its located in the same folder and the structure is the same.

Is there an easy way to make this cell reference automatic to the Sheet1!$D$5 for the latest workbook and updates for each months as time passes?

Upvotes: 1

Views: 761

Answers (2)

Vegard
Vegard

Reputation: 4917

Place this code in the ThisWorkbook module and save the workbook with macros enabled (.xlsm):

Private Sub Workbook_Open()
    ' Change the worksheet to whichever one you need
    Worksheets(1).Range("D5").Formula = "='[" & WorksheetFunction.Proper(MonthName(Month(Now()))) & ".Book1.xlsx]Sheet1'!$D$5"
End Sub

Upvotes: 1

teylyn
teylyn

Reputation: 35915

This is more an issue of data architecture than Excel formulas.

Create the links to a book that is called "CurrentMonth.xlsx".

Then use a procedure that takes the January file and saves it as a file called "CurrentMonth.xlsx". This can be achieved with Powershell.

Next month, you'll get the February file, create a copy and overwrite/rename it to "CurrentMonth.xlsx".

Something like this can be arranged with Powershell or with VBA.

The basic idea here is that you don't change the name of the lookup file, so you don't have to change the formula in the Excel workbook. Rather, use tools at the operating system layer that ensure that the file that the Excel formulas point to is always called the same and is replaced with current data on your schedule.

Upvotes: 3

Related Questions