Get other sheet names into a column
 

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.

i am sure that some vba cose could make each new sheetname automatically add
to your list but in the meantime try this.Type the name of each sheet in a
column and in the next cell to the right type =,navigate to A7 on that sheet
and hit enter.Now make your name into a hyperlink to that sheet.Check out
HYPERLINK in help,there are examples on jumping to worksheets at he bottom of
the page.So you have the name of each worksheet act as a hyperlink to that
worksheet with the value od A7 on that worksheet shown next to it.

paul
remove nospam for email addy!



"Keyser" wrote:

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.

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



"Keyser" wrote in message
...
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.