Evaluating multiple worksheets in a function

Lisette

There must surely be a way of creating a single formula with Excel’s worksheet functions but here are some other ways.
It is assumed that your resume sheet is called “Summary”

Without VBA :-

In cell C4 of each sheet except the Summary sheet enter this formula :-
=IF(C2=2,C3,0)
This puts the C3 value in C4 if the C2 value is 2.
In a cell on the Summary sheet enter a formula to sum all of the other sheets’ C4 cells.

With VBA :-

Both methods below will put the total required in cell C3 of the Summary sheet and will work for any number of sheets but the first sheet must be the Summary sheet.

First method :-
Put the following macro in a normal module. The macro has to be run to update the total on the Summary.

Sub SumSheets()
Dim C As Integer, I As Integer
Dim sumSheet As Worksheet
Set sumSheet = Sheets("Summary")
If Sheets(1).Name <> sumSheet.Name Then
MsgBox "Move the Summary sheet so that it is the first sheet"
Exit Sub
End If
sumSheet.Select
Range("C3").ClearContents
C = Worksheets.Count
For I = 2 To C
If Sheets(I).Range("C2") = 2 Then
Sheets(I).Range("C3").Copy
sumSheet.Range("C3").PasteSpecial Paste:=xlValues, operation:=xlAdd
End If
Next
Application.CutCopyMode = False
End Sub

Second method :-
Put the following in the Summary sheet code module. The total will be updated each time the Summary sheet is selected.

Private Sub Worksheet_Activate()
Dim C As Integer, I As Integer

Making a range for worksheets

Hi Celia!
I want to know how I can simplify the formula in the Summary (Without VBA) so I don't need to write the name of all the sheets.
Is like making a range:
***!B2:^^^^!B2 this means from evaluate cell B2 in every sheet between *** and ^^^^
Thanks!!!