Excel hyperlink to a specific cell

Question

I need to hyperlink a cell in one spreadsheet to a corresponding cell in another spreadsheet. So for example, C7 in sheet1 has a hyperlink that will bring you to C7 in sheet 2. I need to do this for a fairly large spreadsheet and each hyperlink has to be different. Is there a way to do this on mass without having to go to each cell and set the hyperlink for each cell independently? (Using MS Office 2010)

Answer 1

You can use the following excel formula: (paste into cell C7)

=HYPERLINK("[Book1.xlsx]Sheet2!"&CELL("address",C7),"click")

Notes:

• [Book1.xlsx] must be the name of the workbook
• Sheet2 must nbe name name of the sheet you are hyperlinking to

Essentially it uses the above two as a prefix to the link, and then the address of the current cell (c7 in the case of your example) to finish the link.

The above example once pasted into cell C7 can be dragged down to generate links based on the formula cell's address.

Update: (per chris)

=HYPERLINK("#'Sheet2'!"&CELL("address"),"click") 

Answer 2

Sorry to nitpick, it also can look like this:

" - starting quote
# - local book (spreadsheet)
'Sheet2' - name of sheet you are going to (has to be in single quotes)
!C7 - cell in the other sheet you are trying to go to
"- ending quote
, - separating comma used in the hyperlink syntax
"click" - link text to appear in cell

Final function syntax:

=HYPERLINK("#'Sheet2'!C7","click")