VLOOKUP (or VBA Alternative?) From another workbook, depending on Financial Year

[paun_shotts]

I have a workbook that lists products we sell, and how many of each product we have in stock, on order, on backorder for customers, and how many we have sold of each product on any given month over multiple financial years.

=IFNA(VLOOKUP(A20,'Z:\Folder\Info\Company\(Updated Data)\[2024data.xls]Sheet1'!$A$1:$W$1000,3,FALSE),"NF")

I export a report from our system for the current financial year, into this 2024data.xls file.
Then I open the main workbook, and have some VBA code that determines the current financial year, and then it opens that corresponding workbook, which allows the VLOOKUPs to update in the main workbook, then the VBA code closes the 2024data.xls workbook after the values have been updated.

What I want to do is have a dynamic VLOOKUP, or an alternative solution, I have tried the INDIRECT VLOOKUP but that requires the other workbook to be open the whole time. This will NOT work for me.
If the financial year is FY23/24 (I call it 2024 in my workbook, FY24/25 would be called 2025 etc.) Then I want the VLOOKUP to be looking in 2024data.xls, if the financial year is 2025, then VLOOKUP in 2025data.xls etc..

Is there a way to do with NOT using INDIRECT VLOOKUP, possibly with VBA?
If its not possible, would it be possible to prompt the user when they open the main workbook, to select the file to be used for VLOOKUP?

 

[paun_shotts]

Open to any other suggestions on how I can make this work..

 

[paun_shotts]

Ok, I have come up with a separate solution.
Upon opening the workbook, I check the first cell that would contain the VLOOKUP formula, to see if it contains the correct dated data file (2024data.xls for FY 23-24) based on todays date.
If todays date falls between FY24-25 (1/7/2024 - 30/6/2025) then the formulas need to be updated to reference 2025data.xls

Below is the code I used to achieve this solution:

Private Sub Workbook_Open()
   Dim ws As Worksheet
   Set ws = ThisWorkbook.Sheets("Order") ' Replace with your actual sheet name
   
   Dim currentFYStartYear As Long
   currentFYStartYear = DetermineCurrentFYStartYear()
   
   Dim expectedFileName As String
   expectedFileName = currentFYStartYear & "data.xls"
   
   ' Check the formula in cell D5
   Dim checkCell As Range
   Set checkCell = ws.Range("D5")
  
   If Not InStr(checkCell.Formula, expectedFileName) > 0 Then
       ' Informing the user about the update process
       MsgBox "The workbook formulas need to be updated to reference the correct financial year data file. This process may take 1-2 minutes.", vbInformation
       
       Dim col As Range
       Dim cell As Range
       
       Dim regEx As Object
       Set regEx = New RegExp
       regEx.Global = True
       regEx.IgnoreCase = True
       regEx.Pattern = "\[(\d+)data\.xls\]"
       
       For Each col In ws.Columns("D:G").Columns
           For Each cell In col.Cells
               If cell.HasFormula Then
                   If regEx.Test(cell.Formula) Then
                       Dim matches As MatchCollection
                       Set matches = regEx.Execute(cell.Formula)
                       Dim oldFileName As String
                       oldFileName = matches(0).Value
                       
                       Dim newFormula As String
                       newFormula = Replace(cell.Formula, oldFileName, "[" & expectedFileName & "]")
                       cell.Formula = newFormula
                   End If
               End If
           Next cell
       Next col
       
       MsgBox "Formulas in columns D,E,F & G have been successfully updated to reference " & expectedFileName, vbInformation
   End If
End Sub

Function DetermineCurrentFYStartYear() As Long
   Dim currentYear As Long
   Dim nextYear As Long
   Dim currentMonth As Integer
   Dim currentDay As Integer
   
   currentYear = Year(Date)
   nextYear = currentYear + 1
   currentMonth = Month(Date)
   currentDay = Day(Date)
   
   ' Check if current date is before July 1st
   If currentMonth < 7 Then
       DetermineCurrentFYStartYear = currentYear
   Else
       DetermineCurrentFYStartYear = nextYear
   End If
End Function