Simple Hyperlink function assembled with formula still complicated to me

[mrlinking]

I have a quandary with the Hyperlink function in MS Excel, and I'm trying to find the simplest way to make this work.

I am using a simple formula combining various columns of data to assemble a Hyperlink, and I like to see both the formula in a cell, and simultaneously show the result of a formula in the following cell.

It looks something like this (tab separated text simulating Excel)

Start of formula URL End of formula Formula text Result of formula
=HYPERLINK(" Microsoft ? Official Home Page ","View") =HYPERLINK("http://www.microsoft.com/","Link") Link

Note the Formula text column is actually a function =A2 & B2 & C2

I've created the same spreadsheet in OpenOffice Calc, and it behaves the same way. Note in OpenOffice Calc, you need to use a semicolon instead of a coma between the two Hyperlink variables.

Here's what it looks like in Open Office:

* One of the benefits of building a Hyperlink this way is the fact that the URL or address can be updated with a simple Find and Replace, whereas normally the Hyperlink address cannot be edited this way. *

In the final column, I want the text assembled from the first columns to actually function as the formula. At first, I thought what I wanted was to replace the formula with it's result, but perhaps that's not entirely accurate. To do this, you would normally press F2 to edit the cell, and then F9 to convert the formula in the cell to it's value. But doing this merely reduces the Hyperlink formula to the word "View"; the first portion of the Hyperlink formula disappears. Perhaps this is because of the nature of the Hyperlink function or because my formula involves text or a phrase instead of numbers / values? I'm not sure.

Furthermore, when I have a whole list of these links, I can't seem to display the link in the final column dynamically or automatically, and/or I can't find any way to convert the formula text in all columns to function as a formula in one step, within Excel (or OpenOffice Calc), without using macros.

The best I can do is to copy the Formula text column, paste it into Notepad, and then paste it back into Excel or OpenOffice Calc in the next column. After I do this, my hyperlinks magically appear, though I also have to format the text to the standard Hyperlink blue, with underline.

No matter what I try within Excel or Calc, I cannot get a hyperlink to appear from the formula text using Copy and Paste Special, selecting only Values, or any other method.

Is there some other workaround that I'm missing * without running any macros *, or is my technique as good as it gets?

I hope I've managed to explain myself adequately, and perhaps someone reading this will find some inspiration in my wacky workaround methodology.

 

[tygrrboi]

Why not just do away with Column A,

Column B holds the link address and Column C - change to the word "View" instead of all the punctuation.

Then in D (or E), type
=Hyperlink(B2, C2)

 

[mrlinking]

First of all, a big bravo to tygrrboi for your reply!

You turned my quandary into an divinely simple solution!

That is precisely what I was looking for!

=Hyperlink(B2, C2) works instantly, and perfectly!

Beautiful!

Now I'd like to update my original example illustration in order to clarify a few more of the differences I encountered between MS Excel and Open Office Calc when it comes to the =HYPERLINK function.

Just to be clear, the MS Excel Hyperlink function uses a coma between the variables; the OO Calc function uses a semicolon.

(The illustration was originally created in Excel, hence the comas seen in the first three rows, but I finished the example using Open Office Calc, hence the semicolon in the formula bar)

As I originally mentioned, using a function to build your Hyperlink is great because you can use Find and Replace to update your URL if necessary.

You can ALSO link to documents instead of URLs, either with a relative link, or an absolute one.

In MS Excel, an absolute link to a file would look like: H:\Hyperlink\example.jpg and the relative link would simply be: example.jpg or: Subfolder\example.jpg

In Open Office Calc, the distinction between absolute and relative links is a bit more blurred; you first need to make sure relative links are enabled. Tools > Options > Load/Save > General > Save URLs relative to file system.

Using the divinely simplified Hyperlink formula as above, OO Calc needs to see an absolute link that either looks like: H:/Hyperlink/example.jpg OR it could also look like this: file:///H:/Hyperlink/example.jpg

Now you might think that second example is the 'magic relative link' syntax, but that's not quite the case. Here's the caveat with Open Office; the link will only be relative if you create it using the Insert > Hyperlink method. Thus, you can't use a formula to create a relative hyperlink.

When you do use the Insert > Hyperlink method, you'll notice the text field that you input (seen under "Further settings" of the Hyperlink window) becomes an automatic URL. This is the link that is relative, and dynamic. It's extra confusing because it still LOOKS like an absolute path, but if you change your root folder name, your relative hyperlinks are the only links that will continue to work in this spreadsheet.

OO Calc also lets you create a button as a Hyperlink, and you could create that relative link to a file without even needing the formula!

You might begin think that relative links in Open Office are the best!

However, you would still need to create these relative links one at a time, and these relative hyperlinks cannot be edited via find and replace.

Given these limitations, I would just as soon not use relative links at all in OO Calc, and simply stick with the simple formula above to create your links. They can be relative in MS Excel, but make sure they are absolute in OO Calc.

Hopefully, this post will help to shed a little more clarity on the sometimes mysterious HYPERLINK function!