Hyperlink formula from another cell

Hello,
I have a worksheet , in which column B has some text. Column P has a hyperlink in which the last part of the link is variable based on the data in col. B. I have used a formula to concatenate the fixed part of the link and the data from col B. So the hyperlink is displayed correctly in column P and when I click on it the link opens fine. Now what I want to do is hide column P , and instead put the contents of column P ( i.e. the entire hyperlink) in the 'insert hyperlink' box in column B. So can this be done ? I have about 400+ rows in my sheet where this needs to be done.

If I understand you correctly, I don't think you can do that. I.e. Indirectly reference a cell and active the hyperlink through the Insert|Hyperlink box.

You may be able to do it via VBA coding.... for someone else to produce if that is what you want.

.. but not sure if you are aware that there is a Hyperlink() function in Excel that allows you to "display" a friendly name instead of the actual address.

e.g

=HYPERLINK("http://www."&"Yahoo"&".com","Yahoo") will display "Yahoo" in the cell, but will have an underlying hyperlink to Yahoo.com.

So you can insert a colum and make it your new column B and concatenate as you did in column P but within the Hyperlink() function as above, and add the C cell reference in your hyperlink

e.g


=HYPERLINK("http://www."&C1&".com",C1)

Originally Posted by IM2HARD2RESIST

Hello,
I have a worksheet , in which column B has some text. Column P has a hyperlink in which the last part of the link is variable based on the data in col. B. I have used a formula to concatenate the fixed part of the link and the data from col B. So the hyperlink is displayed correctly in column P and when I click on it the link opens fine. Now what I want to do is hide column P , and instead put the contents of column P ( i.e. the entire hyperlink) in the 'insert hyperlink' box in column B. So can this be done ? I have about 400+ rows in my sheet where this needs to be done.

Hi

I'm trying to do the similar things as well.
Great to see your post

Can you share the formula to concatenate the fixed part of the link and the data from col B.
The method I use is

Substitute function is:

Substitute( text, old_text, new_text, nth_appearance )

The end result I hope to achieve is that the datas (in XML) in the url can be display in the cell directly, then maybe another step to clean up the data to only display the results.

Not sure if this step is possible