Excel hyperlink to a specific cell

13
votes

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)

3
excel
Do you know VBA? Also, is it always a 1 to 1 relationship, where it takes you to the exact cell on another sheet? - Scott Holtzman

3 Answers

24
votes

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")
10
votes

Three years late, I would go a bit further and use ADDRESS(row,column) to build the cell address, rather than use CELL() which is a volatile function. If you're building a large spreadsheet and using a volatile function more than just a handful of times, you're going to notice the performance hit.

ADDRESS() is not volatile, so it doesn't trigger a recalculation all the time, and it's also more flexible to use.

=HYPERLINK("#'Sheet2'!"&ADDRESS(ROW(),COLUMN()),"click")

Replace ROW() and COLUMN() with whatever number you require.

For example, for a specific cell in Sheet2 use

=HYPERLINK("#'Sheet2'!"&ADDRESS(ROW(Sheet2!C7),COLUMN(Sheet2!C7)),"click")

If you want Sheet2, third column, and 1 row below (relatively), use

=HYPERLINK("#'Sheet2'!"&ADDRESS(ROW()+1,3),"click")
8
votes

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