Adding TAB names into formulas

[takformaten]

Hello! I have a workbook, with lots of sheets. I am working on a summary sheet now and need a formula to look up data from the various tabs.

Current Formula is =IF(COUNTIF(TABNAMEONE!$A$7:$A$47,Overview!L6)>0,1,0) I would need in the next column the formula to read =IF(COUNTIF(TABNAMETWO!$A$7:$A$47,Overview!L6)>0,1,0).

Quite naturally I am too lazy to type the tab names manually... but you guys always know how... right?

 

[Tinbendr]

Not tested.

Sub VariableSheetNames()
Dim WS As Worksheet
Dim StartRow As Long
'Change to suit
StartRow = 2
For Each WS In ActiveWorkbook.Worksheets
    'Skip the Summary page.
    If WS.Name <> "Summary" Then
        With WS
            'Change Column letter to suit.
            .Range("A" & StartRow).Formula = _
                "=IF(COUNTIF(" & WS.Name & "!$A$7:$A$47,Overview!L6)>0,1,0)"
            StartRow = StartRow + 1
        End With
    End If
Next
 
End Sub

 

[Smitty]

You could do it with INDIRECT.

E.G. =INDIRECT("TabName"&COLUMN()&"!A1")

Placed in column A, COLUMN() returns 1, B = 2, etc.