HYperlink Worksheet Function..

[all4excel]

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

[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

[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

--------------------------------------------------------------------------
--------------------------------------------------------------------------
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.

[davesexcel]

--------------------------------------------------------------------------
--------------------------------------------------------------------------
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

[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

[davesexcel]

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