Excel hyperlink to same sheet

[ashcosta1]

I have an excel workbook with 2 sheets. Sheet1 and Sheet2. I want to stay on sheet1 and create a hyperlink to sheet2 but I want the information that is available in sheet2 to be displayed in sheet1 itself once I click the link... how can I do this?

 

[Toadstool]

Hi Ashcosta1,

HYPERLINK(link_location,friendly_name) can be used.

Let's assume you want to link to Sheet2 cell A10 and display its contents. The formula would be

=HYPERLINK("#Sheet2!A10",Sheet2!A10)

so the friendly name would be the contents of Sheet2 A10 and if you click it it will jump to that cell. The "#" prefix says Sheet2 is in the same workbook.

Excel requires sheet names containing a space be wrapped in single quotes so it's always good practice to wrap the sheet name in single quotes in case it is ever renamed. So it would be

=HYPERLINK("#'Sheet 2'!A10",'Sheet 2'!A10)

I can imagine that you may want to search Sheet2 for something and then link to that location so you can build the HYPERLINK using other functions. Here I enter my animal into Sheet1 A2 and build the hyperlink in B2 which find that animal in Sheet2 column D and links to the same row in column A of Sheet2

Ashcosta1.xlsx
	A	B	C	D
1	Number			Data
2	22222			Dog
3
4
5
6	33333			Cat
7
8
9
10	44444			Mole
11
Sheet2

Ashcosta1.xlsx
	A	B	C
1	Look in Sheet2 Column D	Contents
2	Cat	33333
3
Sheet1

Cell Formulas
Range	Formula
B2	B2	=HYPERLINK("#'Sheet2'!$A$"&MATCH($A$2,Sheet2!D:D,0),INDEX(Sheet2!A:A,MATCH($A$2,Sheet2!D:D,0)))