Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 27th 05, 01:00 AM
Keyser
 
Posts: n/a
Default 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.

  #2   Report Post  
Old August 27th 05, 02:01 AM
paul
 
Posts: n/a
Default

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.

  #3   Report Post  
Old August 27th 05, 02:02 AM
William
 
Posts: n/a
Default

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.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
data from one sheet to several in sequential order! firecord New Users to Excel 6 June 22nd 05 05:10 PM
Lookup Table Dilemma Karen Excel Worksheet Functions 2 June 10th 05 08:22 PM
Does excel recognise names rather than cells? Sue Excel Worksheet Functions 9 May 22nd 05 04:51 AM
Return Count for LAST NonBlank Cell in each Row Sam via OfficeKB.com Excel Worksheet Functions 12 April 17th 05 10:36 PM
Column A is Town, Column B is names. How can Excel add & tell how. Cindy Charts and Charting in Excel 3 January 13th 05 07:27 PM


All times are GMT +1. The time now is 04:23 AM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017