Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data from one sheet to several in sequential order! | New Users to Excel | |||
Lookup Table Dilemma | Excel Worksheet Functions | |||
Does excel recognise names rather than cells? | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Column A is Town, Column B is names. How can Excel add & tell how. | Charts and Charting in Excel |