HYperlink Worksheet Function..

I have a total of 4 sheets and 1 sheet named as Total.
In the Total sheet I have the put the names of other three sheets excluding Total.

For ex:-
1. Tom In B2
2. **** In B3
3. Harry In B4

Now I want to actually go to the other sheets from there..
I know how to use the Insert Hyperlink but these sheets are going to increase..

So I want to make use of the Hyperlink worksheet function but somehow not able to do so..

So that i can move TO & FRO from the Total sheets without manually doing that...

Can anyone guide me ? Please provide the explanation as I have tried going thru the MS Excel explanation but nothing seemed to work..

My formula In C2 would be
=HYPERLINK(LEFT((CELL("filename",$A$1)),LEN(CELL("filename",$A$1))-LEN(RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1),1))))&"Tom"))&B2,A1)


The portion [ HYPERLINK(LEFT((CELL("filename",$A$1)),LEN(CELL("filename",A1))-LEN(RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1),1)))) ] plus "Tom" is the Path and the friendly name is B2..

Please please help me on this....



The information provided in MS Excel...
--------------------------------------------------------------------------
Syntax

HYPERLINK(link_location,friendly_name)

Link_location is the path and file name to the document to be opened as text. Link_location can refer to a place in a document— such as a specific cell or named range in an Excel worksheet or workbook, or to a bookmark in a Microsoft Word document. The path can be to a file stored on a hard disk drive, or the path can be a universal naming convention (UNC) path on a server (in Microsoft Excel for Windows) or a Uniform Resource Locator (URL) path on the Internet or an intranet.

--------------------------------------------------------------------------

all4excel

Try,
=HYPERLINK("#"&CELL("address",INDIRECT(B2&"!A3")),B2)
=HYPERLINK("#"&CELL("address",INDIRECT(B3&"!A3")),B3)
=HYPERLINK("#"&CELL("address",INDIRECT(B4&"!A3")),A4)

=HYPERLINK("#"&CELL("address",INDIRECT(B2&"!A3")),B2)
This will take you to whatever sheet name is in B2 and take you to range A3 of that sheet, the cell also displays whatever is in B2

Originally Posted by davesexcel

Try,
=HYPERLINK("#"&CELL("address",INDIRECT(B2&"!A3")),B2)
=HYPERLINK("#"&CELL("address",INDIRECT(B3&"!A3")),B3)
=HYPERLINK("#"&CELL("address",INDIRECT(B4&"!A3")),A4)

=HYPERLINK("#"&CELL("address",INDIRECT(B2&"!A3")),B2)
This will take you to whatever sheet name is in B2 and take you to range A3 of that sheet, the cell also displays whatever is in B2

--------------------------------------------------------------------------
--------------------------------------------------------------------------
Just excellent,
However Let me tell you that I learnt one very important thing because of you..

This code works very well for 1 word Sheet names but if you have two or more than two word Sheet Names then it goes for a toss.

Modified code- =HYPERLINK("#"&CELL("address",INDIRECT("'"&B2&"'!A3")),B2)

Need to just improvise the code in The Indirect portion to ("'"&B2&"'!A3")

--------------------------------------------------------------------------

Is there any way where I can actually take care of this issue with one code where the Sheet Names are 1/2/3 words..

Like a Function to count the number of words or the spaces..

I know a function- [ FIND(" ",B2,1) ]

Just realised that by adding this portion - ("'"&B2&"'!A3") by default it works well with ant SHeet Name...
--------------------------------------------------------------------------

Thnks a Lot...

all4excel.

Originally Posted by all4excel

--------------------------------------------------------------------------
--------------------------------------------------------------------------
Just excellent,
However Let me tell you that I learnt one very important thing because of you..

This code works very well for 1 word Sheet names but if you have two or more than two word Sheet Names then it goes for a toss.

Modified code- =HYPERLINK("#"&CELL("address",INDIRECT("'"&B2&"'!A3")),B2)

Need to just improvise the code in The Indirect portion to ("'"&B2&"'!A3")

--------------------------------------------------------------------------

Is there any way where I can actually take care of this issue with one code where the Sheet Names are 1/2/3 words..

Like a Function to count the number of words or the spaces..

I know a function- [ FIND(" ",B2,1) ]

Just realised that by adding this portion - ("'"&B2&"'!A3") by default it works well with ant SHeet Name...
--------------------------------------------------------------------------

Thnks a Lot...

all4excel.

That is interesting,
I like using this code sometimes
Worksheet module

Please Login or Register  to view this content.

Selecting a sheet name in column C will take you to that sheet

Originally Posted by all4excel

Great Dave,
Thanks for the code in VBA as well.
But I will stick to the earlier one...

But I did not understand the use of "#" in the formula

[ HYPERLINK("#"&CELL("address",INDIRECT("'"&B2&"'!A3")),B2) ]

Can u please explain that before you close this thread....

I had some problems due to which I was getting disconnected..
I was not able to give an Excellent Rating...

Also does the Rating help you in any way or is it just a way of appreciation?
A token for help..!

Excellent ..Thanks once again..

allexcel

Here is more info on Hyperlinks
http://www.mvps.org/dmcritchie/excel....htm#hyperlink