Referencing a worksheet name

[Greg114]

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.

 

[Bob Phillips]

=SUM(INDIRECT("'"&A1&"'!C418")

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Gord Dibben]

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

 

[Harlan Grove]

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