[Solved] Hyperlink acting odd

[CharlieK1]

I'm getting an odd behavior with Hyperlinks. The hyperlink is supposed to be bringing me to a different sheet. The name of the sheet that its supposed to bring me to is "D151". Instead, its bringing me to CELL D151 in the current sheet.

I'm making the link the way I always have. Using the Hyperlink button... selecting the correct path, clicking on the target button... selecting the correct sheet in the "Target in Document" dialog...

I had this problem before, with an earlier version, and never resolved it.

Any ideas what is happening here?

Is it possible to get this odd behavior if the number of sheets gets too high? I currently have 215 sheets total. Each sheet has up to 100 hyperlinks and/or copy and pasted information from other sheets.

Any help is greatly appreciated!

[acknak]

Greetings and welcome to the community forum!

Try editing the link address manually: #D151.A1 (or whatever cell you want at the top left)

[CharlieK1]

Greetings and welcome to the community forum!

Try editing the link address manually: #D151.A1 (or whatever cell you want at the top left)

How do you edit the link address manually? When I have the cell highlighted that contains the Hyperlink, the link is displayed in the "input" box as a highlighted single unit, that I can't edit...

Want to mention also, that with all of the hyperlinks in this file, none of them are directed to a specific cell in a sheet, just to the sheet itself.

Using Windows 7, if that makes any difference...

[acknak]

... How do you edit the link address manually? ...

Select the cell (you may have to use the arrow keys to avoid clicking on the link)
Insert > Hyperlink ...
There you can edit all the settings for the link

If you have a cell containing a mix of text and links, you may need to position the editing cursor (switch to edit mode, F2) just before the actual link, then Insert > Hyperlink ...

... none of them are directed to a specific cell in a sheet, just to the sheet itself.

Right. The problem is that Calc allows links with a single component and then interprets that component differently depending on whether it's a sheet name or a cell address. When you have sheet names that look like cell addresses (D151 is a cell address, right?) then Calc's can't tell whether the link is to a different sheet or to a cell on the current sheet.

In this case, you have to use a link address that includes both a sheet name and a cell address, otherwise Calc interprets it as a cell address on the current sheet.

Or you can rename the sheet and avoid sheet names that look like cell addresses, even though Calc allows it.

Just FYI, when I have a lot of links to manage in a spreadsheet, I avoid the in-text links. Instead, I suggest keeping the links as plain text and using the HYPERLINK function to create clickable links as needed. The links as plain text are far easier to manage.

[CharlieK1]

I have always assumed that Calc was seeing my sheet name as a Cell name instead of a sheet... Obviously, because my sheet name was, as you say, a cell name. But I have never realized that you could also include a cell along with the sheet in the Hyperlink. It works awesome! Over time, I will get all of the links converted to do exactly the same thing, to try to keep them standard and consistent throughout the entire sheet. Thank you very much for that information!

Could you please clarify what you mean by avoiding the in-text links. I have not explored the other ways to make a hyperlink. All of my links are as follows...

D151..............C:\Users\etc...

The "D151" is plain text. The line of periods are plain text, and their function is simply to move the link to the very edge of the cell. I can click on 90% of the cell, and simply select the cell. If I want to activate the Hyperlink, I have to click on the remaining 10% of the cell. To create this link, I first enter the "D151..........." into the cell, and then use the Hyperlink button to create the Hyperlink, after which, the "C:\Users\etc.." fills in the remaining available space in the cell. To be more correct, only the "C:\Use" is showing in the cell.

Is doing it another way easier to manage?

Thanks!

[acknak]

For example, with a new sheet ...

In A1, type: Sheet2.K23
In B1, type: =HYPERLINK("#"&A1;"Sheet2")

You now have a clickable link in B2. You can make the text anything you want. format it any way you want, and the actual link information is just plain text so you can manage it easily. The only thing you can't get this way is mixed plain text and an active hyperlink, but that's not something I need very often.