Moving text thats hyperlinked

**Frazzfreeman** · 10-17-2007, 04:22 AM

Hi there all,

i have an excel file that has several worksheet tabs most of which i have hyperlinks running on them, what i need to do is colate a couple of the sheets to a seperate excel file but when i try to copy them from the original file to the new file they will not copy because the info is hyperlinked!
So if someone could tell me how i can get around this please!!!!

**Ikaabod** · 10-17-2007, 05:01 PM

Can you elaborate? I did a quick test (using Excel 2003) with a worksheet that has a hyperlink in cell A1 and I was able to create a copy of that worksheet to a new workbook and the hyperlink remained intact. I'm also able to copy the individual cells that are hyperlinked to a new workbook with no problem. Could there be another reason why you are unable to copy your worksheet to another workbook?

**Frazzfreeman** · 10-18-2007, 05:51 AM

Thanks for the reply!
The thing is if i copy the hyperlinked text into a different cell than it's original location i only get the value 0 for example if the original text is in sheet 1 cell A1 and then hyperlinked to sheet 2 cell B2 i can not then copy the information from sheet 2 cell B2 and paste it onto sheet 3 cell 10! just so you are aware i can not just copy the original text because there is additional info on sheet 2! I hope this kind of makes sense, apologies for the poor example!!

**Ikaabod** · 10-18-2007, 04:19 PM

Can you attach an example file (zipped)? I'm not able to recreate your problem on my system, which might just be that I don't fully understand your question. If you just have a file with the hyperlink and the cell it links to and indicate where you want the cell to be copy/pasted to that should suffice.

**Frazzfreeman** · 10-19-2007, 02:57 AM

Hi

Thanks again for the response! I have attached an example of what it is i'm trying to do... Hope this makes things a little clearer!!

**Ikaabod** · 10-22-2007, 12:47 PM

Hi.

Change your formula in Sheet2 cell A1 from:
=Sheet1!A1
to
=Sheet1!$A$1

The dollar signs make for an absolute reference. You can look up absolute reference in excel help to learn more about it. Hope that helps.

**Frazzfreeman** · 10-25-2007, 06:12 AM

Thats great cheers!!!!!!!!

Just one quick question, is there a button or a quick way to change many of these formula's so they are all absolute references, apposed to changing each individual cell if i was to have a whole page full?

**starguy** · 10-25-2007, 07:01 AM

Originally Posted by Frazzfreeman

Thats great cheers!!!!!!!!

Just one quick question, is there a button or a quick way to change many of these formula's so they are all absolute references, apposed to changing each individual cell if i was to have a whole page full?

If you want to change all your references to Sheet1!$A$1 then

Press Ctrl+H
Find what: Sheet1!*
Replace with: Sheet1!$A$1
and press Replace All

Does this work?

**Ikaabod** · 10-25-2007, 01:51 PM

The shortcut key is the function key: F4

While editing the formula in the cell hit F4 and it will automatically put the dollar signs in for you (rather than having to type them yourself). So if your links are all simple one cell references in a single column you can just hit F2, F4, [enter] (and repeat til you get to the last cell). Of course, if it's a huge amount of data a macro can be created to do this task for you.