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: