Hi,

I'm working on a spreadsheet that will probably have 20+ worksheets. I'd
like to make this as 'idiot proof' as possible. I want to create a worksheet
that has clickable links to the page the User wants to view - Can this be
done?

Thanks in advance

Yes it is possible choose insert hyperlink

Leave the link to file box empty

in the named location in file box type a name or for example sheet2!a1

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=522991

Yes it can,
When inserting a hyper link, down the left hand side 'Link To:'
Select 'Place in This Document'

Then choose the appropriate Sheet and Cell or Named Range etc.

George

LavenderBush wrote:
Hi,

I'm working on a spreadsheet that will probably have 20+ worksheets. I'd
like to make this as 'idiot proof' as possible. I want to create a worksheet
that has clickable links to the page the User wants to view - Can this be
done?

Thanks in advance

Wow guy's you're quick :-) when I read Dav's response I saw the 'place in
this document'

Thanks so much, it's been nearly 5 years since I spent any amount of time on
Excel, so I'm still figuring out the updates.

Gotta go and play :-D



"George" wrote:

Yes it can,
When inserting a hyper link, down the left hand side 'Link To:'
Select 'Place in This Document'

Then choose the appropriate Sheet and Cell or Named Range etc.

George

LavenderBush wrote:
Hi,

I'm working on a spreadsheet that will probably have 20+ worksheets. I'd
like to make this as 'idiot proof' as possible. I want to create a worksheet
that has clickable links to the page the User wants to view - Can this be
done?

Thanks in advance

I'm having a problem with this solution -- it seems the hyperlinks are too
much when I'm trying to duplicate pages and make changes -- I need to make
links that are absolute within the sheet itself and don't refer to
files\sheets

ie. I want a link that points specifically to a cell within the sheet but is
not relative to any location. Also I want to be able to make a macro that
allows me to create several links on a page so that I can apply that macro to
many other pages -- this won't work with the way 'hyperlinks' seem to work in
excel - can anyone help?

I'm having this exact same problem using Excel 2003 and cannot find any
information on how to resolve it.

I have a workbook I use for classroom attendance and every time I make any
edits to the template spreadsheet and then copy that spreadsheet to the other
tabs I have to update the links for each class by hand (each class is in a
separate tab spreadsheet). I want to be able to set up one class as a
template and then duplicate/copy that to create the other classes, but it is
a real pain having to update the reference for each hyperlink on each page
each time, because the hyperlinks always reference the original template
spreadsheet.

Any help on this would save me hours of work - the template spreadsheet has
about 60 links and right now I have to update an additional 180 links each
time I make the copies. Thanks!

(PS If this is normal functionality for Excel 2003, is it improved in the
new version of Excel?)

"drsilicone" wrote:

I'm having a problem with this solution -- it seems the hyperlinks are too
much when I'm trying to duplicate pages and make changes -- I need to make
links that are absolute within the sheet itself and don't refer to
files\sheets

ie. I want a link that points specifically to a cell within the sheet but is
not relative to any location. Also I want to be able to make a macro that
allows me to create several links on a page so that I can apply that macro to
many other pages -- this won't work with the way 'hyperlinks' seem to work in
excel - can anyone help?

have you tried "asap utilities' its a free excel addon that makes the large
tedious functions a lot easier. you can just google it and download. it works
awesome.

asap will allow you to create an index sheet (1st work sheet) with hyper
links to each of your sheets/tabs.

"LavenderBush" wrote:

Hi,

I'm working on a spreadsheet that will probably have 20+ worksheets. I'd
like to make this as 'idiot proof' as possible. I want to create a worksheet
that has clickable links to the page the User wants to view - Can this be
done?

Thanks in advance

Rather than hyperlinks maybe a sheet navigation macro?

I prefer Bob Phillips' browsesheet code to go to any sheet in the workbook so
you don't have to keep going back to the TOC sheet.

Sub BrowseSheets()
Const nPerColumn As Long = 38 'number of items per column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select sheet to goto"
'dialog caption


Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim cLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add
With thisDlg
.Name = sID
.Visible = xlSheetHidden
'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(CurrentSheet.Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If
iBooks = iBooks + 1
.OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5
.OptionButtons(iBooks).text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13
Next i
.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24
CurrentSheet.Activate
With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront
Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.OptionButtons
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If
Application.DisplayAlerts = False
.Delete
End With
End Sub

NOTE: Gary Brown revised this code to cover hidden sheets and chart sheets and
other potential problems.

See this thread for that revised code.

http://snipurl.com/1l8o4

I would make one more change to Gary's code.

Const nWidth As Long = 8 'width of each letter
Change to 10 or sheetnames < 4 chars will be incomplete


Gord Dibben MS Excel MVP

On Sun, 5 Aug 2007 04:42:02 -0700, dana c
wrote:

have you tried "asap utilities' its a free excel addon that makes the large
tedious functions a lot easier. you can just google it and download. it works
awesome.

asap will allow you to create an index sheet (1st work sheet) with hyper
links to each of your sheets/tabs.

"LavenderBush" wrote:

Hi,

I'm working on a spreadsheet that will probably have 20+ worksheets. I'd
like to make this as 'idiot proof' as possible. I want to create a worksheet
that has clickable links to the page the User wants to view - Can this be
done?

Thanks in advance