worksheet-name independent hyperlinks

[janzen]

I just found this post on hyperlinks
http://excel.bigresource.com/Track/excel-ClIe789E/

I have a couple questions on that. And was wondering if hopefully somebody would be able to answer them. I really would appreciate if you could help me.

I have a big workbook, with a lot of sheets and every sheet has a number of hyperlinks on it.
1. Now the names of the sheets will change in the future quite often.
2. The hyperlinks that I have in my spreadsheet are linked to certain cells within the same sheet, those cells have been defined with a name. The hyperlinks are linked to that specified name, to prevent the links from failing, when I insert rows or columns
Question: How can I create a hyperlink to those cells, which are defined by a name, so that I don't have to worry about name changes of the sheet?

And here is my second question.

I have cells that are referencing other cells but are supposed to be a hyperlink to a different sheet at the same time. Again that hyperlink has to be independent of the names of the sheets. Is there a possible solution for that? Maybe by using the code name, that you can find in the vb-editor?

I really would appreciate your help. I am stuck on this for a couple days already. Thank you very much.

Jon

 

[Andrew Poulsom]

Welcome to MrExcel.

You can use a formula like:

=HYPERLINK("#Name","TextToDisplay")

Change Name and TextToDisplay to suit.

 

[janzen]

I am sorry, that I didn't answer earlier. Thank you very much for that tip, I will try that, do you maybe any idea, what i can do to have a reference and a link at the same time? in the same cell?

 

[janzen]

I tried your version, unfortunately I get an error message "Reference is not valid". I don't know why. Let me show you what I have.

A8: Content is the word "Orders"; this cell has a defined name connected to it called 'Orders' (all without quotation marks) and I have a long table downwards with more cells like that just with different names. The cell addresses will change over time, since I have to insert more rows regularly.

Above my table I want to list of links to all the little 'headings'

E3: Content is the word "Orders" again, just this time I want this to be hyper linked to the defined name 'Orders'

I tried the following versions of the hyperlink formula:

1. =HYPERLINK("#Orders","Orders")
2. =HYPERLINK(MID(CELL("FileName",A1),FIND("[",CELL("FileName",A1)),255)&"!Orders","Orders")

every time I click on the hyperlink I get the error message, 'Reference not valid' I am not a very experienced person with excel yet, but have to work my way into it. I really would appreciate some help. Thank you very much already.

 

[janzen]

As you can see I need the hyperlink to be independent from the sheet name. I don't know what I am doing wrong. I really would appreciate some help. Thank you very much already.

 

[Andrew Poulsom]

Your first formula worked for me with the setup you describe (Excel 2003):

=HYPERLINK("#Orders","Orders")

 

[janzen]

I am sorry that it took me so long to reply to your answer. Thank you very much for the reply. I tried it out on an empty sheet and a new defined name and it worked perfectly as long as you define the name for the workbook. I guess I forgot an very important detail about the defined name part. The defined name, is defined for that spreadsheet only, because I will have the same name on other worksheets.

I would be interested, how it works for you, when you define a name for a specific worksheet, and change the name of the sheet afterwards. For some reason that is giving me problems then.

Thank you very much for all the tips and help you are giving me.

Jon

 

[janzen]

another thing that might be a problem, do hyperlinks have a problem with Spaces in Sheet-names?

Thank you very much

Jon

 

[Andrew Poulsom]

Try a formula like this:

=HYPERLINK("#'"&SUBSTITUTE(CELL("filename",Sheet2!A1),LEFT(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1),1)),"")&"'!Orders","Orders")