Instant Hyperlink from Text

[Little_Clubber]

Hi all you wonderful people out there. I'm hoping one of you can help me slim down a spreadsheet.

The sheet we have has many columns of data, and for ease, one of those is a Hyperlink, based on another.
For Argument sake, let's say I put a value in A1.
In B1, I have the formula =HYPERLINK("F:\Quality\Start up\2020"&"\"&A1".pdf",A1)
So this creates a hyperlink to the pdf file with the same name, and displays the "friendly name" of the document, which is the exact same information as shown in cell A1.
So A1 and B1 show the same value, one is a hyperlink.

What I would prefer to do is type my data in A1 and upon pressing Tab, Excel automatically change it to the formula I need based on the data that was already entered.
Is this possible please?
Thank you

 

[GWteB]

A VBA procedure can take care of that.
The code below determines if the changed cell is within column A. If that's the case, anything entered will be converted to the hyperlink formula.
The code goes into the worksheet module of the sheet to be affected. Right click on the sheet tab > View Code > paste code in main pane.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const cFormula As String = "=HYPERLINK(""F:\Quality\Start up\2020\@FILE@.pdf"", ""@FILE@"")"
    
    If Not Target.Count > 1 And Not Intersect(Target, Target.Parent.Columns("A")) Is Nothing Then
        Application.EnableEvents = False
        Target.Formula = Replace(cFormula, "@FILE@", Target.Value)
        Application.EnableEvents = True
    End If
End Sub

 

[GWteB]

In case you don't need a formula but a real hyperlink try this ...

Private Sub Worksheet_Change(ByVal Target As Range)

    Const cPath As String = "F:\Quality\Start up\2020\@FILE@"

    If Not Target.Count > 1 And Not Intersect(Target, Target.Parent.Columns("A")) Is Nothing Then
        Application.EnableEvents = False
        With Target.Parent.Hyperlinks.Add(Anchor:=Target, Address:=Replace(cPath, "@FILE@", Target.Value & ".pdf"))
            .TextToDisplay = Target.Value
            .ScreenTip = Target.Value & ".pdf"
        End With
        Application.EnableEvents = True
    End If
End Sub

 

[Little_Clubber]

A VBA procedure can take care of that.
The code below determines if the changed cell is within column A. If that's the case, anything entered will be converted to the hyperlink formula.
The code goes into the worksheet module of the sheet to be affected. Right click on the sheet tab > View Code > paste code in main pane.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    Const cFormula As String = "=HYPERLINK(""F:\Quality\Start up\2020\@FILE@.pdf"", ""@FILE@"")"
   
    If Not Target.Count > 1 And Not Intersect(Target, Target.Parent.Columns("A")) Is Nothing Then
        Application.EnableEvents = False
        Target.Formula = Replace(cFormula, "@FILE@", Target.Value)
        Application.EnableEvents = True
    End If
End Sub

Amazing! Thank you so much! Both of your suggested VBA codes do exactly what I need in terms of the hyperlink, thank you again.
I'm going to use the first one because should we change the folder structure at any point in time and move any existing documents, I can use Ctrl+H and change the path in the existing formulae to keep them working, which I wouldn't know how to do from your second VBA macro.
Thank you again!

 

[GWteB]

You are welcome and thanks for the feedback.

I'm going to use the first one because should we change the folder structure at any point in time and move any existing documents, I can use Ctrl+H and change the path in the existing formulae to keep them working, which I wouldn't know how to do from your second VBA macro.

The macro below would do that, although unlike Ctrl H you cannot edit individual hyperlinks, that would have to be done via the cell context menu.
This macro changes all hyperlinks on the same worksheet pointing to files in the same folder.

Private Sub ChangePathOfHyperlinkedFiles()

    Const cPathOld As String = "F:\Quality\Start up\2020\"
    Const cPathNew As String = "F:\Quality\2020\ArchivedFiles\"

    Dim oHl As Hyperlink
    For Each oHl In ActiveSheet.Hyperlinks
        If StrComp(Left(oHl.Address, (InStrRev(oHl.Address, "\"))), cPathOld, vbTextCompare) = 0 Then
            oHl.Address = Replace(oHl.Address, cPathOld, cPathNew)
        End If
    Next oHl
End Sub