Hi Keyser

Assuming your summary sheet is called "Index" try..


Sub Test()
Dim ws As Worksheet, c As Range
With ThisWorkbook.Sheets("Index")
..Cells.ClearContents
..Range("A1") = "Index"
For Each ws In Worksheets
If Not ws.Name = "Index" Then _
..Range("A65000").End(xlUp).Offset(1, 0) = ws.Name
Next ws
..Columns("A:A").Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
For Each c In _
..Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp)).Offset(0, 1)
c.FormulaR1C1 = "=INDIRECT(""'"" &RC[-1]&""'!A7"")"
..Hyperlinks.Add Anchor:=c.Offset(0, -1), Address:="", _
SubAddress:="'" & c.Offset(0, -1) & "'!A7"
Next c
End With
End Sub

Alternatively, you could place similar code into the worksheet module so the
list is created when you activate the sheet.....

Private Sub Worksheet_Activate()
Dim ws As Worksheet, c As Range
With ThisWorkbook.Sheets("Index")
..Cells.ClearContents
..Range("A1") = "Index"
For Each ws In Worksheets
If Not ws.Name = "Index" Then _
..Range("A65000").End(xlUp).Offset(1, 0) = ws.Name
Next ws
..Columns("A:A").Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlTopToBottom
For Each c In _
..Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp)).Offset(0, 1)
c.FormulaR1C1 = "=INDIRECT(""'"" &RC[-1]&""'!A7"")"
..Hyperlinks.Add Anchor:=c.Offset(0, -1), Address:="", _
SubAddress:="'" & c.Offset(0, -1) & "'!A7"
Next c
End With
End Sub

Have a look at the "Workbook_NewSheet" event as well

--


XL2003
Regards

William
willwest22@yahoo.com


"Keyser" <Keyser@discussions.microsoft.com> wrote in message
news:C1F1C70B-B308-4150-ABAE-8D902EF8ECB2@microsoft.com...
>I am creating a simple envelope budgeting sheet. On my first sheet, I
> would like to have a list of all the other sheets ('envelopes') and
> their balance. Each 'envelope' sheet has its current balance in cell
> A7 and I would like to access that as well.
>
> So my first sheet would have something like:
>
> 8 | 9
> D <name of second sheet>| value of A7 on second sheet
> E <name of third sheet> | value of A7 on third sheet
> ...
>
> If possible, I would like this to be automatic so when I add another
> envelope sheet, its name and A7 value would show up on the first sheet.
>
> Finally, would it be possible to detect a double click on the sheet
> name and
> then switch to that sheet?
>
> Thanks in advance for any help.