I'm sure there are several ways to do this,
but I've never done the hyperlink  to a hidden  sheet.
What I usually do is use a selectionchange event in the
Index sheet. test to see if the selection is one of the specific cells (or
value, or...) ,
then use a macro to unhide the sheet  and select it.
You can then use a deactivate event (or the activate event for the index  sheet)
to hide the other sheet.
need help with this?

it's better to create a VBA that "looks" into the cell and leaves
you to the hidden  sheet (unhiding it before).

I have a sheet  here where I created a menu like this. The old guys made a harder
solution, with ComboBox and white/hidden cells... so I tried hyperlinks, but
with hyperlinks I have noticied that my users was not able to update the menu
when needed. So, my solution was made this VBA. Users can create new sheets  and
the only work is add a new line on this simple menu.

And about the original solution, I keep only the "hide others" feature, but
changing the "For 2 to XX" to "For 2 to Sheets.Count", thinking on a error-free
solution when the user add more sheets.

Normally, the focus is immediately transferred to the hyperlink
address on clicking into a cell containing a hyperlink. Are we saying
there is no way of adding any vba to this cell that unhides the
hidden sheet  THEN sends you to the destination ?

One method of doing this would be to simply hide the Tabs so
it appears that the only available sheet  is the one that you are
presently in. This does not prevent a user from moving to another
sheet with keystrokes but hiding the Tabs does not require code.

The problem is that the "hide" functionality was DESIGNED to make the sheet
inacessible
without removing it.
At least temporarily.
The hyperlink  REQUIRES an accessible location.
You might be able to use the hyperlink to link to some other object that could
then, in turn, run the VBA, but I think this works "cleaner":
Here's what I did:
I have a spreadsheet that has a tab called "Summary", and a whole bunch of data
tabs.
In a module, I added the macros:
'==============================
Option Explicit
Dim I
Sub Refresh_List()
Dim inx
Range("C3:C65000").ClearContents
inx = 2
For I = 1 To Sheets.Count
If (UCase(Sheets(I).Name) <> "SUMMARY") Then
inx = inx + 1
Cells(inx, 3) = Sheets(I).Name
End If
Next I
End Sub
Sub Hide_Sheets()
Application.ScreenUpdating = False
For I = 1 To Sheets.Count
If (UCase(Sheets(I).Name) <> "SUMMARY") Then
Sheets(I).Visible = False
End If
Next I
Application.ScreenUpdating = True
End Sub
' (for Debugging purposes only)
Sub Unhide_Sheets()
Application.ScreenUpdating = False
For I = 1 To Sheets.Count
Sheets(I).Visible = True
Next I
Application.ScreenUpdating = True
End Sub

'==============================
In the VBA for the "Summary" sheet, I added:
'==============================
Private Sub Worksheet_Activate()
Hide_Sheets
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
If (Target.Value <> "") And (Not Intersect(Range(Target.Address),
Range("C3:C65000")) Is Nothing) Then
Application.ScreenUpdating = False
Sheets(Target.Value).Visible = True
Sheets(Target.Value).Select
Cancel = True
Application.ScreenUpdating = True
End If
End Sub
'==============================
If you run "Refresh_List", it will find all of the tab names and put them in a
list starting in cell C3.
Now, if you double-click on any of the tab names, it will unhide the tab and
select it.
When you select the "Summary" tab, it hides all the other sheets.
Seems to work pretty clean to me.
If you have formulas that you need to add to the Summary tab for each data tab,
you can have the macro insert the formula.

"Are we saying there is no way of adding any vba to this
cell that unhides the hidden  sheet THEN sends you to the destination ?"

I say "NO, it's not impossible", but think with me: Since VBA will be necessary,
why don't we create a simple code that does ALL the service?? If we can do it,
our users will don't need to worry about hyperlinks... just put the sheetnames
and description. Today I had to create another sheet  like this, see my solution:

'MENU SHEET:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Dim S As String
Dim N As Integer
S = ActiveCell.Offset(0, -1).Value
For N = 2 To Sheets.Count
Sheets(N).Visible = Not MENU.Range("hideall").Value
Next
Sheets(S).Visible = True
Sheets(S).Select
Err.Clear
End Sub

Well done. This is all.

I am turning this file  available on the net, please find the link below to see
how it works:
http://www.galvao.pro.br/excel/menu_vba_links.xls