Create a tracking link based on data from two cells

alex_345

New Member
Joined
Jul 13, 2022
Messages
7
Office Version
  1. 365
Platform
  1. MacOS
  2. Web
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:

Screen Shot 2022-07-13 at 12.54.25 PM.png
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Hello!

Try paste this code to the Sheet module, it works for Purolator only(i don't know links templates for other tracking services):
VBA Code:
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
 
Upvote 0
Hello!

Try paste this code to the Sheet module, it works for Purolator only(i don't know links templates for other tracking services):
VBA Code:
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="
 
Upvote 0
After copying this code to your Sheet module, try input "Purolator" or "UPS" to G20, then input any number to H20.
VBA Code:
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
 
Upvote 0
After copying this code to your Sheet module, try input "Purolator" or "UPS" to G20, then input any number to H20.
VBA Code:
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?
 
Upvote 0
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?
 
Upvote 0
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
ABCDEFGHIJKL
1NameCust namePhoneCity, PRDateDateCarrierTracking #LinkItem namePieces
2AMBob123-456-7890City1/1/20221/1/2022Purolator3331234567891
3AMBob123-456-7891City1/1/20221/1/2022Purolator333124567891
4UPS123
5UPS123456
6Purolator
7Purolator
8
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K10:K1048576,K1:K7Expression=$H$5="Purolator"textNO
Cells with Data Validation
CellAllowCriteria
G2:G7ListUSPS, FedEx, Purolator, UPS
 

Attachments

  • Screen Shot 2022-07-18 at 1.29.46 PM.png
    Screen Shot 2022-07-18 at 1.29.46 PM.png
    240.8 KB · Views: 9
Upvote 0
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:
Excel 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")
 
Last edited by a moderator:
Upvote 0
Solution
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.
IMG_20220718_211011.png

I used this formula:
Glad you solved your problem with formula, a macro is just another method. :)
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top