Hi All,

At work we use Excel'97, and I have a workbook that has two worksheets in it. The first sheet contains hyperlinks to locations on our Intranet. The second sheet is a summary of the first.

Text is copied across to the second sheet correctly, but the hyperlinks are not copied (just the text).

Any ideas for a solution?

TIA

Daniel

 

Hello Daniel,

Rather than using the insert hyperlink command, use the hyperlink formula instead. This can be copied and pasted keeping the hyperlink and text intact.

Cheers,
Dunk

 

Weird ...

I've used "CTRL K" to insert a hyperlink [to a website] and also the HYPERLINK[hyperlink_name) formula to do this.

What I want to do now is give the hyperlink a name so you don't see the URL. Any ideas. Doesn't work on the formula I used, copied here from MS Help ...

HYPERLINK(link_location,friendly_name)

 

This works for me:

=HYPERLINK("http://bbs.scoobynet.co.uk","ScoobyNet")

The hyperlink is displayed as ScoobyNet

Not the same on yours ?

 

Thanks Dunk, I'll give that a go this afternoon.

Daniel

 

Dunk,

Unfortunately, that hasn't worked.

I need the data in the summary sheet to be dynamic, so rather than doing a straight copy-and-paste, I use the formula:

=Master!Cell Ref

Even using the '=hyperlink' formula on the 'Master' sheet does not copy the hyperlink to the summary sheet, just the text.

Getting a bit peeved off now , unless the might of ScoobyNet can come up with an alternative solution

Daniel

 

I copy/pasted your bit of code into my spreadsheet and Hey Presto! it works

Does not work if I type the code in though. Excel is very straaaange

Thanks Dunk

 

Can you make the formula a variable and refer to that in your hyperlink code?

eg

Dim MASTER as variant

MASTER =Master!Cell Ref

HYPERLINK(MASTER, "Master")

 

I'm getting a little confused here Do you two work together or is this 2 separate problems ?

You can use cell references within the formula, just do.

=HYPERLINK(Master!,A1)

This will copy the hyperlink including text to a cell on another sheet. Is this what you mean ?

 

There's only one of me. And some say that's more than enough

I paid a visit here as I was really getting frustrated with naming a hyperlink. I wrote the code exactly the same as your example but it did not want to work. Even checked with an old version of the spreadsheet and saw no differences :Confused: No worries, it works now

Most of the macro's I create in excel are using vba which is why my coding is always long-winded

 

Thanks for your help Dunk...

I tried your suggestion and it still wouldn't work, so in the end I extracted all of the hyperlinks into a seperate column, then used the 'hyperlink' command to combine the hyperlinks with the text.

Daniel