SEARCH

The How-To Geek Forums Have Migrated to Discourse

How-To Geek Forums / Microsoft Office

(Solved) - Excel 2010 and hyperlinks

(15 posts)
  • Started 2 years ago by zidnod
  • Latest reply from zidnod
  • Topic Viewed 19296 times

zidnod
Posts: 29

Hi Everyone,

I have a strange problem that just appeared the last couple of days. In a spreadsheet that I had hyperlinks created they no longer work, and I have no idea why. I can create a new hyperlink in a new work book and that will work fine. But if I paste the data from this old spreadsheet into a new work book to create new hyperlinks they won't work. Or I had one hyperlink that would only work if I pressed enter on it and then arrowed down and back up to it and pressed enter again and then Excel would jump to the correct cell. Anybody have any experience with anything like this with hyperlinks in Excel 2010 and Windows 7.

Thanks.

zidnod

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

Hi zidnod

Moving a hyperlink that is pointing to a cell in another sheet in the same workbook will not work in another Excel work book if the sheet names are different. As a test, right click on a cell with a hyperlink and click on Edit Hyperlink and see what it says under "Edit cell reference" and this may give you a clue as to what it is trying to link to and why it won't work, or post a screen shot of the pop-up so that we can diagnose the problem.

Posted 2 years ago
Top
 
zidnod
Posts: 29

Hi Moreeg,

I did the verification of the hyperlink and everything was OK. Even in the original work book the hyperlinks all worked fine for months but now they don't. And nothing changed I didn't rename the file or anything so I can't see why they now don't do anything. And if there was a error message that would pop up I would tell you but nothing happens.

Posted 2 years ago
Top
 
scorpion99
scorpion99
Posts: 1033

How are you pasting? Normally ? or using Paste Special????

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

Hi Scorpion
The pasting would seem to be a secondary problem. The real problem seems to be that the links in that one workbook have been corrupted and when zidnod tries to copy them to a new workbook the corruption is transferred as well. When he recreates the link it works fine.

@zidnod
A shot in the dark is to check the link parameters in Advanced Options and see if they look like this

You could also try a "repair" but again I doubt this will help since the problem is constrained to the one workbook only - but repair is worth a try since it won't do any damage.

It may be that the only solution is to recreate the whole workbook with as little copy and paste as possible so that the corruption isn't carried over.

Posted 2 years ago
Top
 
zidnod
Posts: 29

Hi Moreeg,

I'm trying to recreate the workbook, with no pasting what so ever. And I have created a few hyperlinks just to see how they will work. But they still appear to be a little flaky. Tell me something should my hyperlinks look like this:
=HYPERLINK("[PROFILE.XLSX]SHEET1!K1", K1)

You know in the beginning I never put sheet1 in my hyperlink string I didn't do this until I tried to hyperlink from sheet1 to sheet2.

The new hyperlinks in the new work book all have the sheet1 line in them. But when I press enter on them they only work maybe ever fourth time.

And I feel a little slow right now I finally saw the tip to do a repair. Can you give me some kind of idea what this repair is . At this point I would be willing to try anything.

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

is PROFILE.xlsx a different workbook than the one where you have the hyperlink?
how are you creating the hyperlink? are you entering the =hyperlink... statement in the cell?

Posted 2 years ago
Top
 
zidnod
Posts: 29

Hi Moreeg,

No, that hyperlink formula is on that new spreadsheet that I'm recreating the original without copying and pasting. And I am hand typing these hyperlinks just as you stated.

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

So what are you trying to link to and from where ?

Is it from one excel file to another excel file on the same computer in the same directory?

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

OK let's try again

I don't understand if you are typing in the hyperlink location why you would be surprised to see SHEET1 as part of the statement.

The hyperlink statement you have will work one of 2 ways

The workbook that contains the hyperlink is called PROFILE.XLSX and when you click on it it will take you to sheet 1 cell k1 in PROFILE.xlsx

The workbook that contains the hyperlink is another workbook (let's call it BOOK1.XLSX) and when you click on the link it will take you to sheet 1 cell k1 in PROFILE.xlsx but ONLY if PROFILE.XLSX is in the same directory as BOOK1.XLSX

So you need to tell me the name of the workbook and the sheet name and cell that contains the hyperlink AND
you need to tell me the name and location of the workbook and the sheet name and the cell that you are trying to link to

.

Posted 2 years ago
Top
 
zidnod
Posts: 29

Hi Moreeg,

Okay, let's see if I can explain what is going on here at my end. First let's look at the hyperlink string:
=HYPERLINK("[PROFILE.XLSX]SHEET1!K1", K1)
This hyperlink is a same work book hyperlink. Meaning I press enter on a hyperlink in Profile.xlsx in cell A1 and Excel jumps me to K1 in the same work book and on the same spreadsheet. My original hyperlinks I created from the beginning never had a reference to what sheet the hyperlink was associated with. I just came across this when I tried to create a hyperlink from sheet1 to Sheet2. I should point out that in the old work book when I created a link to sheet2 all the other links that had no reference to sheet1 stopped working. This might have been the cause of the corruption.

All my hyperlinks are sheet1 in the same work book and I still have a real problem with them. They don't work the very first time I press enter on them.

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

Hi zidnod

Your hyperlink statement is fine except that if you want to link to K1 in sheet2 you would have

=HYPERLINK("[PROFILE.XLSX]SHEET2!K1", K1)

The second K1 refers to K1 in Sheet1 and the contents of that cell would appear in the cell with the hyperlink.

Another, possibly better way (the way I prefer) is to set up your links by right clicking in the cell where you want the hyperlink

Try these and see if they work for you

Posted 2 years ago
Top
 
zidnod
Posts: 29

Hi Moreeg,

Can you give me a little tutorial on how to use the right click hyperlink menu option. I can never figure out what I'm suppose to type into the path name. If I type the same string as we have been talking about Excel gives me a warning that the file can not be opened.

Posted 2 years ago
Top
 
moreeg
moreeg
Posts: 842

If you are linking to another sheet in the same workbook see my previous post. There is no typing required and ....

As a rule, you should not be typing anything - there are too many odd characters that Excel needs to make it practical.

If you want to link to another workbook here is what you will see after right clicking and selecting Hyperlink

- on the extreme right make sure "Existing file ..." is selected
- to the right of that "Current folder" should be selected
- if you want to link to a workbook in another folder you can navigate to the directory with the navigation buttons (the folder icons)
- find the workbook you want to link to in the resulting list and click on it (once only) (also note that you can link to any type of file - not just Excel)
- click on the Bookmark button on the right
- this will pop up another window which contains a list of sheets in the workbook you selected
- select the sheet you want
- at the top select the cell you want (Excel assumed A1) and press OKAY
- If you want the linking cell in the original workbook to say something descriptive e.g. "Click here to open xxxx.xls" then type that onto the "Text to Display" field at the top.

If after you've set up the link you want to change something then right click on the cell and select edit hyperlink

-

Posted 2 years ago
Top
 
zidnod
Posts: 29

Hi Moreeg,

Thanks for all the help. I got it all to work thanks to your excellent instructions. And now all my hyperlinks work great every time.

Accepted Answer · Posted 2 years ago
Top
 



Topic Closed

This topic has been closed to new replies.