Greetings,
When I enter a tracking ID in Cell A2 (just a static value), I would like to be able to make that a clickable link relative to a specific URL syntax at a carrier's website. I can accomplish that by concatenating the URL with the tracking ID value in Cell A2. Understandably, I get the results of the concatenated string whereby:
- the result of the function is not a HYPERLINK, and
- the actual displayed result is the URL
What I would love to happen is to have the value of what's in Cell A1 be the result of the function and have it be a clickable hyperlink. A bonus would even be to have just one column for this (possible embed the function into a conditional formatting) where whenever the tracking ID is pasted into Column A, it automatically generates the desired result.
THE CHALLENGE:
The URL syntax works for FedEx. The moment I use a different carrier, the solution won't work. I have a list of URL syntax for various carriers. Is it possible to use a more complex solution that perhaps involves Vlookup or Index/Match functions to accomplish this? I suppose I would need an additional column to identify the tracking ID as a particular carrier to know which URL syntax to use, wouldn't I?
Thanks!
When I enter a tracking ID in Cell A2 (just a static value), I would like to be able to make that a clickable link relative to a specific URL syntax at a carrier's website. I can accomplish that by concatenating the URL with the tracking ID value in Cell A2. Understandably, I get the results of the concatenated string whereby:
- the result of the function is not a HYPERLINK, and
- the actual displayed result is the URL
What I would love to happen is to have the value of what's in Cell A1 be the result of the function and have it be a clickable hyperlink. A bonus would even be to have just one column for this (possible embed the function into a conditional formatting) where whenever the tracking ID is pasted into Column A, it automatically generates the desired result.
Excel 2013 32 bit | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | Tracking ID | URL | CURRENT RESULT | DESIRED RESULT | ||
2 | 123456789 | https://www.fedex.com/apps/fedextrack/?action=track&trackingnumber=TESTSEARCH&cntry_code=ca_english | https://www.fedex.com/apps/fedextrack/?action=track&trackingnumber=123456789&cntry_code=ca_english | 123456789 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2 | ="https://www.fedex.com/apps/fedextrack/?action=track&trackingnumber="&A2&"&cntry_code=ca_english" |
THE CHALLENGE:
The URL syntax works for FedEx. The moment I use a different carrier, the solution won't work. I have a list of URL syntax for various carriers. Is it possible to use a more complex solution that perhaps involves Vlookup or Index/Match functions to accomplish this? I suppose I would need an additional column to identify the tracking ID as a particular carrier to know which URL syntax to use, wouldn't I?
Thanks!
Last edited: