Create a tracking link based on data from two cells

[alex_345]

Hello,
I'll start by saying I'm a little past novice with the power of Excel, but also not super intricately skilled—so hoping someone with more knowledge can assist!

I am trying to have excel create a tracking link based on which carrier an employee selects in Column G and the tracking # in Column H. Provided that each carrier tracking follows a similar link formatting, I would copy their link, but just need to change the tracking # ... for example, Purolator: Tracker where the number after "pin=" would change for each link, like the other carriers. I just need to create a link, based on the conditions, in Column I. If needed, I could also store the link beginnings somewhere within the workbook, if needed to make this work.

Any solutions to do this ... formulas, formatting, commands? I would appreciate it!


This is a sample of what our workbook already looks like:

 

[LazyBug]

Hello!

Try paste this code to the Sheet module, it works for Purolator only(i don't know links templates for other tracking services):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim carrier As String, addressPur As String
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 8 And Target.Row >= 10 Then
        If Target.Offset(, 1) <> "" Or IsEmpty(Target.Offset(, -1)) Then
            Exit Sub
        Else
            carrier = "Purolator": addressPur = "https://www.purolator.com/en/shipping/tracker?pins="
            If Target.Offset(, -1).Value = carrier Then _
            ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, 1), Address:=addressPur & Target.Value, TextToDisplay:=carrier & " web link"
        End If
    End If
End Sub

 

[alex_345]

Hello!

Try paste this code to the Sheet module, it works for Purolator only(i don't know links templates for other tracking services):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim carrier As String, addressPur As String
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 8 And Target.Row >= 10 Then
        If Target.Offset(, 1) <> "" Or IsEmpty(Target.Offset(, -1)) Then
            Exit Sub
        Else
            carrier = "Purolator": addressPur = "https://www.purolator.com/en/shipping/tracker?pins="
            If Target.Offset(, -1).Value = carrier Then _
            ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, 1), Address:=addressPur & Target.Value, TextToDisplay:=carrier & " web link"
        End If
    End If
End Sub

Hello! Thanks so much for this!

I did try pasting into the Sheet 1 module, but nothing happened when I made changes to the sheet. Might I be doing something wrong?

Also, if I want to add the other carriers, what would I add to the code ... what would I copy and where to paste it in the code (then I can change the carrier and link for each and repeat)?
Another example link would be UPS Tracking | UPS - United States and the # goes after the "Number1="

 

[LazyBug]

After copying this code to your Sheet module, try input "Purolator" or "UPS" to G20, then input any number to H20.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim carr As String, addr As String
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 8 And Target.Row >= 11 Then
        carr = Target.Offset(, -1).Value
        
        If Target.Offset(, 1) <> "" Or carr = "" Then
            Exit Sub
        Else
            Select Case carr
                Case "Purolator"
                    addr = "https://www.purolator.com/en/shipping/tracker?pins="
                Case "UPS"
                    addr = "http://wwwapps.ups.com/WebTracking/processInputRequest?TypeOfInquiryNumber=T&InquiryNumber1="
                Case "USPS"
'                    addr = "http"
                Case "FedEx"
'                    addr = "http"
            End Select
            ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, 1), Address:=addr & Target.Value, TextToDisplay:=carr & " web link"
        End If
    End If
End Sub

 

[alex_345]

After copying this code to your Sheet module, try input "Purolator" or "UPS" to G20, then input any number to H20.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim carr As String, addr As String
    If Target.Count > 1 Then Exit Sub
    If Target.Column = 8 And Target.Row >= 11 Then
        carr = Target.Offset(, -1).Value
       
        If Target.Offset(, 1) <> "" Or carr = "" Then
            Exit Sub
        Else
            Select Case carr
                Case "Purolator"
                    addr = "https://www.purolator.com/en/shipping/tracker?pins="
                Case "UPS"
                    addr = "http://wwwapps.ups.com/WebTracking/processInputRequest?TypeOfInquiryNumber=T&InquiryNumber1="
                Case "USPS"
'                    addr = "http"
                Case "FedEx"
'                    addr = "http"
            End Select
            ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(, 1), Address:=addr & Target.Value, TextToDisplay:=carr & " web link"
        End If
    End If
End Sub

I tried pasting the code into Sheet 1 module again, then entering "Purolator" in the G Column and any numbers in H Column, but it still is not doing anything. Am I missing something? The link is supposed to appear in Column I right?

 

[LazyBug]

I tried pasting the code into Sheet 1 module again, then entering "Purolator" in the G Column and any numbers in H Column, but it still is not doing anything

Correct. Does your table locate at the Sheet1?

 

[alex_345]

Correct. Does your table locate at the Sheet1?

Yes, I have everything from the original picture on sheet 1 (uploaded a mini-sheet below for reference). For the code, I pasted the new one in Visual Basic window by right-clicking on "Sheet 1">Insert>Module. Then saved and made changes in the sheet, but nothing happened; no link is created in Column I.

If there is any way, could you message me and I can send my sample sheet to ensure it is set correctly? Or any other solution?

Book.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Cust name	Phone	City, PR	Date	Date	Carrier	Tracking #	Link	Item name	Pieces
2	AM	Bob	123-456-7890	City	1/1/2022	1/1/2022	Purolator	333123456789			1
3	AM	Bob	123-456-7891	City	1/1/2022	1/1/2022	Purolator	33312456789			1
4							UPS	123
5							UPS	123456
6							Purolator
7							Purolator
8
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K10:K1048576,K1:K7	Expression	=$H$5="Purolator"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G2:G7	List	USPS, FedEx, Purolator, UPS

 

[alex_345]

Correct. Does your table locate at the Sheet1?

After weeks of trying to find some way to do this, I figured out a solution!!!! I used the following formula and it was able to combine the link start and the tracking #, then hyperlink it.

Thanks so much for your suggestions and assistance @LazyBug !

I used this formula:

=HYPERLINK(IFS(G2="Purolator", "Tracker"&H2, G2="UPS", "Tracking | UPS - United States"&H2, G2="FedEx", "https://www.fedex.com/apps/fedextrack/?action=track&tracknumbers="&H2, G2="Canada Post", "https://www.canadapost-postescanada.ca/track-reperage/en#/filterPackage?searchFor="&H2), "LINK")

 

[LazyBug]

For the code, I pasted the new one in Visual Basic window by right-clicking on "Sheet 1">Insert>Module. Then saved and made changes in the sheet, but nothing happened

I get it. You saved code in a Code Module. Double-click the Sheet1 object to Sheet Module open, then paste macro there.

I used this formula:

Glad you solved your problem with formula, a macro is just another method.