If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Referencing a worksheet name
Hello,
I have a workbook with 10-15 worksheets with different names. I am trying to set up a "Summary" worksheet that will have each worksheets name in column A and then two summed columns with data from each worksheet (C4-D18 and D31-E52). Is there a way to reference the worksheet names in Column A within a formula? For example, in place of SUM(Worksheet1!C418) I'd like to reference the worksheet name in column A - SUM("Column A"!C418). Thanks in advance for any assistance. |
#2
|
|||
|
|||
=SUM(INDIRECT("'"&A1&"'!C418")
-- HTH RP (remove nothere from the email address if mailing direct) "Greg114" wrote in message ... Hello, I have a workbook with 10-15 worksheets with different names. I am trying to set up a "Summary" worksheet that will have each worksheets name in column A and then two summed columns with data from each worksheet (C4-D18 and D31-E52). Is there a way to reference the worksheet names in Column A within a formula? For example, in place of SUM(Worksheet1!C418) I'd like to reference the worksheet name in column A - SUM("Column A"!C418). Thanks in advance for any assistance. |
#3
|
|||
|
|||
And to easily list the names into column A
Private Sub ListSheets() 'list of sheet names starting at A1 Dim Rng As Range Dim i As Integer Set Rng = Range("A1") For Each Sheet In ActiveWorkbook.Sheets Rng.Offset(i, 0).Value = Sheet.Name i = i + 1 Next Sheet End Sub Gord Dibben Excel MVP On Wed, 23 Mar 2005 20:05:00 -0000, "Bob Phillips" wrote: =SUM(INDIRECT("'"&A1&"'!C418") |
#4
|
|||
|
|||
Gord Dibben wrote...
And to easily list the names into column A Private Sub ListSheets() 'list of sheet names starting at A1 Dim Rng As Range Dim i As Integer Set Rng = Range("A1") For Each Sheet In ActiveWorkbook.Sheets Rng.Offset(i, 0).Value = Sheet.Name i = i + 1 Next Sheet End Sub .... Declared *Private* in order to force the OP to run it from the VBE or from another macro?! Simple fubar or sadistic streak? Better to use a udf and allow the OP to decide where the list should appear (as well as allowing the list to update on full recalcs or kludged volatile recalcs). Function slst( _ Optional t As String = "CMS", _ Optional r As Range _ ) As Variant '------------------------------------------------------------- 'optional 1st arg specifies which sheets to include in results 'using last char of XL4 worksheet extensions: xlC - charts, 'xlM - macros, xlS - [work]sheets -- all other chars ignored 'optional 2nd arg specifies which *OPEN* workbook's sheets '1st defaults to all sheets, latter defaults to workbook which 'contains the calling formula. '------------------------------------------------------------- Const C As Long = 1, M As Long = 2, s As Long = 3 Dim rv As Variant, tt(1 To 3) As Boolean, x As Variant Dim n As Long If r Is Nothing Then If TypeOf Application.Caller Is Range Then Set r = Application.Caller Else Set r = ActiveCell End If End If If InStr(1, t, "C", vbTextCompare) 0 Then tt(C) = True If InStr(1, t, "M", vbTextCompare) 0 Then tt(M) = True If InStr(1, t, "S", vbTextCompare) 0 Then tt(s) = True ReDim rv(1 To r.Parent.Parent.Sheets.Count) For Each x In r.Parent.Parent.Sheets If (x.Type = -4169 And tt(C)) _ Or ((x.Type = xlExcel4MacroSheet _ Or x.Type = xlExcel4IntlMacroSheet) And tt(M)) _ Or (x.Type = xlWorksheet And tt(s)) Then n = n + 1 rv(n) = x.Name End If Next x ReDim Preserve rv(1 To n) slst = Application.WorksheetFunction.Transpose(rv) End Function |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
WorkSheet / ActiveSheet Question | Brian | Worksheet Functions | 1 | August 25th, 2004 08:51 PM |
inserting worksheet name in cell using function | Worksheet Functions | 2 | May 25th, 2004 07:11 PM | |
inserting worksheet name in a cell using function | GUS | Worksheet Functions | 1 | May 25th, 2004 01:40 AM |
&[tab] Usage Twist | Jody | Worksheet Functions | 4 | January 10th, 2004 06:21 PM |
Sheet Names | Joseph M. Yonek | Worksheet Functions | 6 | January 3rd, 2004 02:15 AM |