Using a date in a reference file name

Question

I am using a formula which has a reference to an outside excel spread sheet as the reference location which pulls data from a particular file. The reference location uses a file name with a date in the name. Rather than go line by line and changing the date in the file name can I give the file name a relative reference that refers to the date in column a as shown below.

Date	mmscfd
2024-01-01	='R:\Countess - Corporate_Shared\Control Room\Prorated production report\2024  Prorated Reports[1-1-2024.xlsm]Flowrates'!$D$19
2024-01-02	='R:\Countess - Corporate_Shared\Control Room\Prorated production report\2024  Prorated Reports[1-2-2024.xlsm]Flowrates'!$D$19
2024-01-03	='R:\Countess - Corporate_Shared\Control Room\Prorated production report\2024  Prorated Reports[1-3-2024.xlsm]Flowrates'!$D$19
2024-01-04	='R:\Countess - Corporate_Shared\Control Room\Prorated production report\2024  Prorated Reports[1-4-2024.xlsm]Flowrates'!$D$19
2024-01-05	='R:\Countess - Corporate_Shared\Control Room\Prorated production report\2024  Prorated Reports[1-5-2024.xlsm]Flowrates'!$D$19

Answer 1

This macro will loop through all the cells in column B and update the file path based on the date in column A.

Sub UpdateFilePath()

Dim ws As Worksheet 

Dim lastRow As Long 

Dim cell As Range 

Dim dateValue As String 

Dim filePath As String 

Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name 

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 

For Each cell In ws.Range("B1:B" & lastRow) 

    If IsDate(ws.Cells(cell.Row, 1).Value) Then 

        dateValue = Format(ws.Cells(cell.Row, 1).Value, "m-d-yyyy") 

        filePath = "='R:\Countess - Corporate\_Shared\Control Room\Prorated production report\2024  Prorated Reports\[" & dateValue & ".xlsm]Flowrates'!$D$19" 

        cell.Value = filePath 

    End If 

Next cell 

End Sub