Best way to link to another place in the workbook?

Brian

Board Regular
Joined
Apr 24, 2002
Messages
113
What is the best way to link to another place in an XL97 workbook?

I could not find any bookmark or goto function. I could do it with VBA and a button, but I prefered a simple hyperlink equation.

My solution was:
S4: =CELL("filename",References!$C$1)

N1: =HYPERLINK(MID($S$4,SEARCH("[",$S$4),LEN($S$4)-SEARCH("[",$S$4)+1)&"!$A$1","REFERENCE")

This works and will accomodate both workbook name and sheet name changes but will fail if the target cell $C$1 is deleted.

I tried Insert > hyperlink, but that failed as soon as I saved the workbook with a new name.

Thanks in advance

Brian
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi Brian,

Have you tried changing from absolute to
relative i.e $C$1 to C1?

James
 
Upvote 0
What's your objective ? Just have value in one cell be reflected in the second ?
I feel I'm not understanding the problem.
 
Upvote 0
The objective is to have a hyperlink in a cell labelled "Help" that will jump to the "help" sheet.

That's what the existing formula does, but i expected there to be a simpler method (like bookmarks in a word doc).
 
Upvote 0
How about using the "custom view" feature on the "views" menu.
You select the sheet where your help is. Make this a custom view and then just select this view from the "drop down" view list that will appear on the tool bar .
This message was edited by Nimrod on 2002-05-04 22:35
 
Upvote 0
Well the way that i would do it would be to use Defined names. On the "Help Sheet", select the cell (i.e. A1) that you would like the link to take you to and then go INSERT->NAME->DEFINE.
Now just type what you would like to call the define name. I would suggest just calling it Help. Now go back to the original sheet that will have the hyperlink on it. You can either use a button or just type text "Help" and hyperlink that. When using the hyperlink, make sure you use the Defined name of Help as the hyperlink. Not sure how XL97 will prompt you for this, so i can't help you there. Now after you do that, when you click the text or the button it will bring you to that Defined Name which will be on your "Help" Sheet.
I like this method better than just using the hyperlink as "Help!A1" because it will always bring you to that cell even if the tab name has been changed.
Let me know if this works for you. Hopefully i completely understood your question.

Greg
 
Upvote 0
Create a view
Change the settings that you want to save in the view. Learn about settings that are saved when you create a view.
On the View menu, click Custom Views.
Show Me
Click Add.
In the Name box, type a name for the view.
Under Include in view, select the options you want.


Display a view
On the View menu, click Custom Views.
In the Views box, click the name of the view you want.


Source is excel help ... just search for infor on "custom views"
 
Upvote 0
The nice thing with using the custom view is that you can be on any page of the workbook and access the help sheet by just clicking on it's view on the tool bar.
If you use a particular cell for the helplink then you have extra steps to get to help that don't occur with the custom view.
 
Upvote 0
Thanks to all.

Yes, Insert Hyperlink > Named Location > Browse > Defined Name > Name does work and withstands file and sheet name changes.

{Note that Insert Hyperlink > Named Location > Browse > Sheet Name > Sheet fails if the sheet name changes, but is ok when the filename changes.)

(Note that simply hyperlink(Name,"Name") does NOT work.)

Custom views are cool! Except that I need to add code to ensure that the custom toolbar is shown (yes, already attached it tho the workbook).

Thanks again,

Brian
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top