Building Error Checks into my Worksheet

#2

Formula:

=SHEETS()-2

Not exactly considering excluding specific names, but if you know you have those sheets in the file you just subtract the number of sheets you want to exclude from the count from the total number of sheets in the file.

#1

Do the number of sheets in the workbook change (or better asked 'should they change')? You say pulling a value from each detail sheet, is that value always in the same place on each sheet (A1 on each detail sheet)?

If the sheets are pretty static and the value you are pulling (I am guessing a count of rows) is the same then you can easily create a list of sheet names and then next to each just reference that cell on that sheet to get the value.

A sample file would help us help you. Any further detail/explanation would enable us to provide more detailed solutions.

I would avoid VBA with this if possible. So far nothing in your request seems to require VBA, and using VBA will require saving as a macro enabled file, require others to enable macros, and could break over time (making it harder to fix than a formula).

You could use VBA but you do not need to

in B2 copy down
=INDEX(SheetNames,A2)
in C2 copy down
=INDIRECT(B2&"!A1")

Named range "SheetNames" with RefersTo:
=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")

Produces this
sheetList.jpg

You can see that all the sheets are there with #REF - Excel cannot find a 10th sheet

The named range is an Excel4 macro and so the workbook must be saved as macro enabled

VBA alternative
- test in attached workbook wih {CTRL} k
- sheet name AND value of cell A1 from each sheet (except excluded sheets) inserted in Sheet "ErrorCheck"

How it works
- array of excluded sheet names created
- all sheets looped looking for match in excuded array
- if match not found list is updated

Sub ListSheetValues()
    Dim excl, n As String, s As Integer, NotIsInArray As Boolean, ws As Worksheet, cel As Range
    Set ws = Sheets("ErrorCheck")
    excl = Array("Summary", "ErrorCheck")        'sheets to exclude - in quote marks separated by comma
    ws.Cells.Clear
    ws.Range("A1:B1") = Array("SheetName", "Value of A1")
    
    For s = 1 To ThisWorkbook.Sheets.Count
        n = Sheets(s).Name
        NotIsInArray = IsError(Application.Match(n, excl, 0))
        If NotIsInArray Then
            Set cel = ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
                cel = n
                cel.Offset(, 1) = Sheets(s).Range("A1")
        End If
    Next s
End Sub

The next check I would like to do simply counts the number of tabs in the workbook if your thier name isnt 'Summary', 'Error Check' or any other tabs I define.

Sub countTabs()
    Dim excl:    excl = Array("Summary", "ErrorCheck")
    MsgBox ThisWorkbook.Worksheets.Count - UBound(excl) - 1
End Sub