Hyperlink Function to Tab

[Matt]

I'm trying to create a hyperlink using the hyperlink function that
will go to a separate tab within the same worksheet. What I'm using
is: =HYPERLINK("'Tab1'!A1","Cell1") , but it is telling me it
cannot open the specified file. Can anyone help?

 

[Pete_UK]

Try it like this:

=HYPERLINK("#'Tab1'!A1","Cell1")

Note the apostrophe is after the # - this basically means "the current
file".

Hope this helps.

Pete

 

[Matt]

That work perfectly. Now the next step would be to use that to create
a sort of index. The tab names are listed in column A. Using the
hyperlink function in column B, it would create a hyperlink to the tab
based on whats in column A. I thought I could use a combination of
hyperlink and indirect, but I think my syntax is wrong (?)

=HYPERLINK(INDIRECT(""#'"&A1&"'!A1""),"Cell1"), where Tab1 is typed in
the A1 cell

Any ideas?

 

[Hutch]

I am not sure about the syntax, but at least in Excel 2007 you can create a
hyperlink to another place in a document by right-clicking on a cell,
choosing Hyperlink, and then on the left-hand side of the window that opens
choose 'Place in this document'.

 

[Dave Peterson]

David McRitchie posted this and it might help you:

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

The thing that's very nice about David's syntax is that if you insert or delete
rows/columns in Sheet3, then the formula will point to the new location.

You could also use this syntax:
=HYPERLINK("#Sheet3!C5","Click Here")

Will always point at C5 of Sheet3--no matter if you insert/delete rows or
columns.

So you'll want to choose the one that fits your requirements.