Live look ups to a file name that changes for version control

[taubeam]

Before I go bananas trying to figure out how to do this, can this be done?

I have to maintain a live link to a file that my co-worker names with a version number frequently.

Is there anyway to link for a look up to this file with maybe a wild card to account for the versioning? or to default to the last modified?

For example the name "02.Field Quota Planning 2018v##"
Is there anyway to update the look up to account for the changing version numbers and use the most recently saved version?

 

[John_w]

It probably can be done, but you need to be more specific. What do you mean by a look up and a live link? Is it a VLOOKUP or a HLOOKUP formula which references an external workbook? If so, you could have a VBA procedure which recreates the formula(s) when the main workbook is opened and calls the Dir function to loop through the external workbooks in a folder and calls the FileDateTime function to determine the last modified workbook.

The code at https://www.mrexcel.com/forum/excel...closed-workbooks-post4723265.html#post4723265 does something similar and could be adapted to your situation.

 

[taubeam]

It probably can be done, but you need to be more specific. What do you mean by a look up and a live link? Is it a VLOOKUP or a HLOOKUP formula which references an external workbook? If so, you could have a VBA procedure which recreates the formula(s) when the main workbook is opened and calls the Dir function to loop through the external workbooks in a folder and calls the FileDateTime function to determine the last modified workbook.

The code at https://www.mrexcel.com/forum/excel...closed-workbooks-post4723265.html#post4723265 does something similar and could be adapted to your situation.

Thanks, for looking.

The formula is below
=INDEX('[02. Field Quota Planning 2018 v34.xlsx]All Reps'!$M:$M,MATCH(K4,'[02. Field Quota Planning 2018 v34.xlsx]All Reps'!$A:$A,0))

Where V34 is changed nearly every day

 

[John_w]

Try this code in a standard module in your main workbook. Run the Create_Formula macro to create the =INDEX formula - which references the latest matching file in the same folder as the main workbook - in cell A2 of Sheet1. You could call this from the Workbook_Open event handler.

Public Sub Create_Formula()

    Dim latestFileName As String
    
    latestFileName = FindLatestFile(ThisWorkbook.Path & "\02. Field Quota Planning 2018 v*.xlsx")
    
    If latestFileName <> "" Then    
        Workbooks.Open ThisWorkbook.Path & "\" & latestFileName
        ThisWorkbook.Worksheets("Sheet1").Range("A2").Formula = "=INDEX('[" & latestFileName & "]All Reps'!$M:$M,MATCH(K4,'[" & latestFileName & "]All Reps'!$A:$A,0))"  
    Else
        MsgBox "No files found matching '02. Field Quota Planning 2018 v*.xlsx' in " & ThisWorkbook.Path
    End If
    
End Sub


Private Function FindLatestFile(fullFileSpec As String) As String

    Dim folder As String, fileName As String
    Dim latestFileDate As Date
    
    FindLatestFile = ""
    
    folder = Left(fullFileSpec, InStrRev(fullFileSpec, "\"))
    latestFileDate = 0
    fileName = Dir(fullFileSpec, vbNormal)
    Do While fileName <> vbNullString
        If FileDateTime(folder & fileName) > latestFileDate Then
            FindLatestFile = fileName
            latestFileDate = FileDateTime(folder & fileName)
        End If
        fileName = Dir
    Loop
    
End Function

 

[taubeam]

Try this code in a standard module in your main workbook. Run the Create_Formula macro to create the =INDEX formula - which references the latest matching file in the same folder as the main workbook - in cell A2 of Sheet1. You could call this from the Workbook_Open event handler.

Public Sub Create_Formula()

    Dim latestFileName As String
    
    latestFileName = FindLatestFile(ThisWorkbook.Path & "\02. Field Quota Planning 2018 v*.xlsx")
    
    If latestFileName <> "" Then    
        Workbooks.Open ThisWorkbook.Path & "\" & latestFileName
        ThisWorkbook.Worksheets("Sheet1").Range("A2").Formula = "=INDEX('[" & latestFileName & "]All Reps'!$M:$M,MATCH(K4,'[" & latestFileName & "]All Reps'!$A:$A,0))"  
    Else
        MsgBox "No files found matching '02. Field Quota Planning 2018 v*.xlsx' in " & ThisWorkbook.Path
    End If
    
End Sub


Private Function FindLatestFile(fullFileSpec As String) As String

    Dim folder As String, fileName As String
    Dim latestFileDate As Date
    
    FindLatestFile = ""
    
    folder = Left(fullFileSpec, InStrRev(fullFileSpec, "\"))
    latestFileDate = 0
    fileName = Dir(fullFileSpec, vbNormal)
    Do While fileName <> vbNullString
        If FileDateTime(folder & fileName) > latestFileDate Then
            FindLatestFile = fileName
            latestFileDate = FileDateTime(folder & fileName)
        End If
        fileName = Dir
    Loop
    
End Function

Thank you so much, this worked great.

We're trying to add an auto fill now. I will have a great deal of formulas in different columns referencing this file. That's why I wanted to not have to remember all the places its linked.

Our auto fill tries have not worked. If you have time can you help with ours below?


Public Sub Create_Formula()


Dim latestFileName As String

latestFileName = FindLatestFile("\\Slnas3\nas\lib-SL\Sales\sales_enablement\001 New Stuff\Comp Plans\2018\Quota Planning" & "\02. Field Quota Planning 2018 v*.xlsx")
lastRow = Range("S4").End(xlDown).Row


If latestFileName <> "" Then
Workbooks.Open "\\Slnas3\nas\lib-SL\Sales\sales_enablement\001 New Stuff\Comp Plans\2018\Quota Planning" & "" & latestFileName
ThisWorkbook.Worksheets("Detail").Range("T4").Formula = "=INDEX('[" & latestFileName & "]All Reps'!$M:$M,MATCH(K4,'[" & latestFileName & "]All Reps'!$A:$A,0))"
Else
MsgBox "No files found matching '02. Field Quota Planning 2018 v*.xlsx' in " & ThisWorkbook.Path
End If
Range("T4").AutoFill Destination:=Range(Range("T4"), Range("T" & lastRow))



End Sub

 

[John_w]

The Workbooks.Open makes the opened file the active workbook and your AutoFill statement uses an unqualified Range reference so it uses the active sheet in the active workbook. Try this:

Public Sub Create_Formulas()

    Dim lastRow As Long
    Dim planningFolder As String
    Dim latestFileName As String
    
    planningFolder = "\\Slnas3\nas\lib-SL\Sales\sales_enablement\001 New Stuff\Comp Plans\2018\Quota Planning"
    lastRow = ThisWorkbook.Worksheets("Details").Range("S4").End(xlDown).Row
    latestFileName = FindLatestFile(planningFolder & "\02. Field Quota Planning 2018 v*.xlsx")
    
    If latestFileName <> "" Then
        Workbooks.Open planningFolder & "\" & latestFileName
        With ThisWorkbook.Worksheets("Details")
            .Range("T4").Formula = "=INDEX('[" & latestFileName & "]All Reps'!$M:$M,MATCH(K4,'[" & latestFileName & "]All Reps'!$A:$A,0))"
            .Range("T4").AutoFill Destination:=.Range(.Range("T4"), .Range("T" & lastRow))
        End With
    Else
        MsgBox "No files found matching '02. Field Quota Planning 2018 v*.xlsx' in " & planningFolder
    End If
    
End Sub

Please use CODE tags when posting code - click the # icon in the message editor.

 

[taubeam]

The Workbooks.Open makes the opened file the active workbook and your AutoFill statement uses an unqualified Range reference so it uses the active sheet in the active workbook. Try this:

Public Sub Create_Formulas()

    Dim lastRow As Long
    Dim planningFolder As String
    Dim latestFileName As String
    
    planningFolder = "\\Slnas3\nas\lib-SL\Sales\sales_enablement\001 New Stuff\Comp Plans\2018\Quota Planning"
    lastRow = ThisWorkbook.Worksheets("Details").Range("S4").End(xlDown).Row
    latestFileName = FindLatestFile(planningFolder & "\02. Field Quota Planning 2018 v*.xlsx")
    
    If latestFileName <> "" Then
        Workbooks.Open planningFolder & "\" & latestFileName
        With ThisWorkbook.Worksheets("Details")
            .Range("T4").Formula = "=INDEX('[" & latestFileName & "]All Reps'!$M:$M,MATCH(K4,'[" & latestFileName & "]All Reps'!$A:$A,0))"
            .Range("T4").AutoFill Destination:=.Range(.Range("T4"), .Range("T" & lastRow))
        End With
    Else
        MsgBox "No files found matching '02. Field Quota Planning 2018 v*.xlsx' in " & planningFolder
    End If
    
End Sub

Please use CODE tags when posting code - click the # icon in the message editor.

Thank you John W

I wish I was eloquent enough to provide my thankfulness. This site and people like you is why the internet is awesome.