Hyperlink

[anee_4285]

Hello All,

Its regarding Hyperlink in Ms 2013. I have two sheets names
Sheet 1-Devices
Sheet 2-Switches

In Devices sheet, i have A1 with the some no. (ex:1010)

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]A[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]10101[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]10102[/TD]
[/TR]
</tbody>[/TABLE]

In Switches sheet, I have a list of some details regarding Sheet 1 (Device)

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]10106[/TD]
[TD="class: xl65"]12[/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]10110[/TD]
[TD="class: xl65"]13[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]10101[/TD]
[TD="class: xl65"]14[/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]10104[/TD]
[TD="class: xl65"]15[/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]10102[/TD]
[TD="class: xl65"]16[/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]10109[/TD]
[TD="class: xl65"]17[/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]10108[/TD]
[TD="class: xl65"]18[/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]10107[/TD]
[TD="class: xl65"]19[/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]10105[/TD]
[TD="class: xl65"]20[/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]10103[/TD]
[TD="class: xl65"]21[/TD]
[/TR]
[TR]
[TD="class: xl65"]11[/TD]
[TD="class: xl65"]10111[/TD]
[TD="class: xl65"]22[/TD]
[/TR]
</tbody>[/TABLE]

Now my question is can we automaticly search for the hyperlink from Sheet 1 to sheet 2. Like when i click on 10101 in Sheet 1 it has to jump into sheet 2 to A3. I cant map maually becuase i have to do it 1000+ cells.

Thanks in advance.

 

[FormR]

Hi - you could give this a try:

Excel 2012
	A	B
2	10101	Click here
3	10102	Click here
Devices

Cell Formulas
Range	Formula
B2	=HYPERLINK("#Switches!A"&MATCH(A2,Switches!A:A,0),"Click here")

Excel 2012
	A	B
1	10106	12
2	10110	13
3	10101	14
4	10104	15
5	10102	16
6	10109	17
7	10108	18
8	10107	19
9	10105	20
10	10103	21
11	10111	22
Switches

 

[AlanY]

on Sheet1 column B and copy down will create the links to Sheet2

=HYPERLINK("#'Sheet2'!" & ADDRESS(MATCH($A1, Sheet2!$A:$A, 0), 2), "Link")

 

[anee_4285]

Hi - you could give this a try:

Excel 2012

	A	B
Click here
Click here

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]
[TD="align: right"]10101[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]10102[/TD]

</tbody>

Devices​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=HYPERLINK("#Switches!A"&MATCH(A2,Switches!A:A,0),"Click here")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]




Excel 2012

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]10106[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]10110[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]10101[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]10104[/TD]
[TD="align: right"]15[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]10102[/TD]
[TD="align: right"]16[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]10109[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]10108[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]10107[/TD]
[TD="align: right"]19[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]10105[/TD]
[TD="align: right"]20[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10103[/TD]
[TD="align: right"]21[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]10111[/TD]
[TD="align: right"]22[/TD]

</tbody>

Switches​

Thanks a lot.. its working.