Hyperlink refers back to previous sheet?

**simonwilliams** · 11-30-2009, 07:40 AM

I have recently added 3 x hyperlink to my spreadsheet as its now some 138 rows in length.

All works fine with the 3 x hyperlinks & a click of whichever one moves me to different sections of the spreadsheet.

My problem is that when I copy the worksheet (right click on the tab, select move or copy) & then make a copy the hyperlink refers back to the original copy & I have to manually change it to relate to it's own copy.

is their anyway to stop Excel doing this so the hyperlinks when copied all relate to their own worksheet?

Thanks for any help.

**Rahul Nagar** · 11-30-2009, 07:52 AM

Hello,

I think you can try Past Special.

Regards
Rahul

**simonwilliams** · 11-30-2009, 08:01 AM

Hi

That option isn't available. To duplicate worksheets I right click on the tab of the current work sheet, select move or copy, choose to create a copy & move to end.

Any other ideas?

**Rahul Nagar** · 11-30-2009, 08:05 AM

Hello

Please Select all the data press Ctrl+A and after Paste into second sheet with Past Special.

I think this would help you

Rahul

**ncmay** · 11-30-2009, 08:06 AM

Are you using the HYPERLINK function?

Here is just one example:

Please Login or Register  to view this content.

**Jack in the UK** · 11-30-2009, 08:22 AM

You can hyperlink almost anywhere

Add this into a cell, its what i generally do

=HYPERLINK("#"&CELL("address",Sheet2!A1))

Now the cell will read - #[Book1]Sheet2!$A$1 this is expected

**simonwilliams** · 12-09-2009, 07:05 AM

Hi again

I've tried the copy & paste method but the hyperlinks just refer back to the worksheet from which the copy was made.

Their must be a way to have hyperlinks within a worksheet that link to various point within that worksheet & then when that worksheet is copied & pasted the hyperlinks will link to the new worksheet?

Otherwise each time I do this I have to edit each hyperlink pointing it to the current worksheet?

Any advice?

**davesexcel** · 12-09-2009, 07:19 AM

Originally Posted by simonwilliams

Hi again

I've tried the copy & paste method but the hyperlinks just refer back to the worksheet from which the copy was made.

Their must be a way to have hyperlinks within a worksheet that link to various point within that worksheet & then when that worksheet is copied & pasted the hyperlinks will link to the new worksheet?

Otherwise each time I do this I have to edit each hyperlink pointing it to the current worksheet?

Any advice?

?? you have two answers that could work for you, instead you decided to respond to the answer that does not work for you.

**teylyn** · 12-09-2009, 07:23 AM

Simon, the technique that Jack suggests works.

when constructing a hyperlink with "insert hyperlink" you are creating a unique, absolute reference that will not change when the sheet is copied. The hyperlink will still refer to the absolute reference.

A hyperlink with the formula provided by Jack, though, when referencing a cell in the same sheet, will work as you want. If the sheet is copied, the hyperlink on the copy will jump to the cell in the copy.

Have you tried it?