+ Reply to Thread
Results 1 to 9 of 9

trying to find a way to automate the Shipment Tracking number column

  1. #1
    Registered User
    Join Date
    08-07-2015
    Location
    Washington
    MS-Off Ver
    365, v1808 32bit
    Posts
    59

    trying to find a way to automate the Shipment Tracking number column

    I have an excel sheet that is a record of all orders my company ships to a customer, which I email to them at the end of the week. This is an ongoing sheet that's updated every day which includes all pertinent info of their orders, and in one column I list the shipment carrier ("UPS, FedEx, etc) and in the column next to it, I enter the tracking number. I'm trying to find a way to automate the tracking number column. (I'd like to avoid VBA if at all possible)

    When I type in a tracking number into a cell, I would like that cell to change based on the value I am inputting.

    So for example if I type this tracking number "ABC123" in cell A1, I would like that same cell to turn into this: =HYPERLINK("http://wwwapps.ups.com/etracking/tracking.cgi?TypeOfInquiryNumber=T&InquiryNumber1=ABC123")

    The trick is I use 3 different shippers, so this link won't always point to UPS. So I'm wondering if I can use conditional formatting some how so whenever I type "ABC***" It will change to a link as shown above, but then if I type something like "XYZ***" it can point to a different link, like DHL for example: =HYPERLINK("https://dhli.dhl.com/dhli-client/publicTracking?searchType=HBN&searchValue=XYZ***")
    Last edited by FDibbins; 01-18-2016 at 11:12 PM.

  2. #2
    Forum Contributor
    Join Date
    10-10-2014
    Location
    New Jersey
    MS-Off Ver
    2010 & 2013
    Posts
    276

    Re: Shipment Tracking Automation (conditional formatting maybe)

    Does the link have to be returned in the same cell that you enter the tracking number in or can it be returned in the cellnext to it?

  3. #3
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Shipment Tracking Automation (conditional formatting maybe)

    hello truefangz, can you upload a sample book
    with 4-5 websites name that will be used in your hyperlink
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,908

    Re: Shipment Tracking Automation (conditional formatting maybe)

    The key lies with VLOOKUP. Create a table with the prefixes you want and associate URLs like on Sheet 2.
    Attached Files Attached Files

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Shipment Tracking Automation (conditional formatting maybe)

    Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

    Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

    To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Ben Van Johnson

  6. #6
    Registered User
    Join Date
    08-07-2015
    Location
    Washington
    MS-Off Ver
    365, v1808 32bit
    Posts
    59

    Re: Shipment Tracking Automation (conditional formatting maybe)

    Hi All,

    Thanks for your replies!!

    Quote Originally Posted by Teblol View Post
    Does the link have to be returned in the same cell that you enter the tracking number in or can it be returned in the cellnext to it?
    Ideally. I currently have a way of doing it to the cells next to the tracking, but want to eliminate the need for the extra columns.

    Quote Originally Posted by hemesh View Post
    hello truefangz, can you upload a sample book
    with 4-5 websites name that will be used in your hyperlink
    Something similar to what dflak uploaded would work. See below

    Quote Originally Posted by dflak View Post
    The key lies with VLOOKUP. Create a table with the prefixes you want and associate URLs like on Sheet 2.
    This seems like a great option, only exception is, currently if I type the tracking number "ABC123" in cell C3, it then puts the link in D3. How I would like this to perform is I would type the tracking number "ABC123" in cell B3, and it would automatically change B3 to what D3 currently does.

    So is there a way that when I type "ABC***" it could automatically change the cell data do do the lookup function? Though I'd like to avoid using VBA, if that's the only solution, please let me know how I would accomplish this, and I may be able to use that as a last resort. Problem is that some of my customers have high security, and won't accept any macro contained workbooks.

    Thanks again for everyone's responses and the help!!

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Shipment Tracking Automation

    TrueFangz, please comply with the request made by Proton Leigh
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  8. #8
    Registered User
    Join Date
    08-07-2015
    Location
    Washington
    MS-Off Ver
    365, v1808 32bit
    Posts
    59
    Quote Originally Posted by FDibbins View Post
    TrueFangz, please comply with the request made by Proton Leigh
    I thought I did. What's wrong with it now?

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: trying to find a way to automate the Shipment Tracking number column

    I have changed it to something that better explains what you want

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Shipment Tracking - UPS- DHL - FEDEX
    By iowitz in forum Excel General
    Replies: 2
    Last Post: 07-20-2014, 11:48 PM
  2. Conditional formatting for vacation tracking
    By pwatkins in forum Excel General
    Replies: 0
    Last Post: 01-20-2014, 11:45 AM
  3. [SOLVED] Conditional Formatting for Attendance Tracking
    By SciGuy in forum Excel Formulas & Functions
    Replies: 23
    Last Post: 12-03-2013, 01:26 AM
  4. [SOLVED] Conditional Formatting date tracking
    By az6781 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2013, 12:01 AM
  5. Integrate basic ebay API for upload of Shipment Tracking Info with Excel?
    By bnasty in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-08-2012, 03:08 PM
  6. Excel 2007 : Conditional Formatting: Delta Tracking
    By lanied in forum Excel General
    Replies: 16
    Last Post: 07-21-2011, 04:43 PM
  7. Gantt Chart Help with Conditional Formatting Automation
    By starbwoy in forum Excel General
    Replies: 9
    Last Post: 12-15-2009, 06:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1