Excel 2010: create hyperlinks from two columns

Question

I've got a spread sheet arranged thus:

LinkName1      http://www.link1url.com    MoreData1a    MoreData1b ...

LinkName2      http://www.link2url.com    MoreData2a    MoreData2b...

...

LinkNameN     http://www.linkNrl.com    MoreDataNa       MoreDataNb...

How can I create a new column that displays LinkNameX from the first column but hyperlinks to http://www.linkXurl.com in the second column?

Answer 1

Let's say your data start in cell A2, i.e. LinkName1 in A2, http://www.link1url.com in B2 etc.

In another cell in row 2, enter the formula

=HYPERLINK(B2,A2)

Fill or copy this formula down as far as needed.

Answer 2

In another cell in row 2, enter the formula

 

=HYPERLINK(B2,A2)

That's what I tried first, it doesn't seem to work. The cell just shows the formula. 

Could this be because the data started life as an XML file?

[UPDATE]

It appears that I have a Table in my sheet. If I allow the expression builder to insert the cells into the HYPERLINK function then I get =HYPERLINK(Table1[@page], Table1[@name]) where "page" and "name" are at the top of the columns in question. I thought they were just the first row headings, but it seems that Excel has performed some database function based on the original XML file.

Answer 3

Make sure that the cells with the formulas are not formatted as Text. If a cell is formatted as text, a formula entered in the cell is seen as plain text, it will not act as a formula.

It does not really matter whether the data are in a table or not. If they are in a table, you'll get so-called structured table references in your formula such as Table1[@page], but the formula should still work if the cell isn't formatted as Text.