I’m working in an xlsx file that I share with a company that uses MS Office. I have a hyperlink formula with a nested VLOOKUP formula that concatenates the value from a tracking number cell and the value from a drop-menu cell. When I first created the sheet, the hyperlinks all Ctrl-Clicked just fine. However, after sharing the spreadsheet back and forth with the MS Office user a few times, the hyperlink cells all display the hover tool-tip message “Ctrl-Click to follow link:Link”. Upon Ctrl-clicking the link, an error message states " ‘Link’ is not an absolute URL that can be passed to an external application to open it." This seems normal enough except for the fact that the tool-tip message usually displays the entire link and not the link text which is “Link” in this case. To temporarily bypass this bug, I click on the drop-menu and select the another choice or re-select the existing choice. The hyperlink then becomes active again and operates as expected, launching a new web browser window or tab.
Why is this happening? What can I do differently to avoid re-clicking all of the drop menu cells to “bump” the hyperlink cell formulas into “re-freshing” themselves? This is my best explanation of what is happening. Please see the formula below:
=IF(S800="","",IF(T800="","",HYPERLINK(IF(T800=“Fedex”,CONCATENATE(“https://www.fedex.com/apps/fedextrack/?tracknumbers=",S800),IF(T800=“UPS”,CONCATENATE(“https://wwwapps.ups.com/WebTracking/track?track=yes&trackNums=”,S800),IF(T800=“USPS”,CONCATENATE(“https://tools.usps.com/go/TrackConfirmAction?tLabels=”,S800),IF(T800=“DHL”,CONCATENATE(“http://www.dhl.com/en/express/tracking.html?brand=DHL&AWB=”,S800))))),"Link”)))