sum worksheets with a certain criteria

[Tracy]

I have created a worksheet for each individual person. I am trying to create
a formula that will filter though the different worksheets and sum the
people who have started on a specific date. Can somebody please tell me how
to do this or if it can even be done.

Thank you,

Tracy

 

[Bill Pfister]

Are your worksheets dynamic name and quantity (i.e. are the sheet names Bob,
Joe, Bill, etc. or sheet1, sheet2, sheet3 and do you frequently add and
remove sheets)? Is each individual's sheet similary structured (are you
looking at the same cell or range of cells on each sheet)?

Regards,
Bill

 

[Tracy]

The sheets are named Bob, Joe Bill etc. And yes, I do add and move sheets
frequently. All the sheets are copied and so they are the same structure. I
am looking for the same cell on each sheet. What I am trying to do is if a
person started on a particular date which is in cell A1 than add their
tuition which is in C1. I have done some reading on the THREED formula, but
when I use that I get a #REF! error. I really appreciate your help with
this. Thank you so much.

 

[Bill Pfister]

You can use this user-defined function to accomplish what you need.

This formula goes into a cell on your summary sheet:
=SumSalaries( "5/15/2005", "Sum1", "Sum3", "A1", "B1" )
Instead of literal values in quotation marks, you could also refer to cells
on your summary sheet that contain the corresponding values.
"5/15/2005" is the desired date.
Sum1 & Sum3 are the first and last of the individual sheets, respectively.
All other individual sheets must be between Sum1 & Sum3.
A1 is the address that contains the date on each individ. sheet. B1 is the
address to sum.

Put this code into a module in VBA:

Public Function SumSalaries(strCheckDate As String, strSheetStart As String,
strSheetEnd As String, strAddressCheck As String, strAddressSum As String) As
Double
Application.Volatile

Dim wkb As Workbook
Dim wks As Worksheet
Dim lngStart As Long
Dim lngEnd As Long
Dim i As Long
Dim dblTotal As Long

Set wkb = ThisWorkbook

If (Not (ExcelSheetExists(wkb, strSheetStart))) Then
Call MsgBox("Sheet (" & strSheetStart & ") does not exist!")
Exit Function
End If

If (Not (ExcelSheetExists(wkb, strSheetEnd))) Then
Call MsgBox("Sheet (" & strSheetEnd & ") does not exist!")
Exit Function
End If

lngStart = wkb.Worksheets(strSheetStart).Index
lngEnd = wkb.Worksheets(strSheetEnd).Index

strCheckDate = Format(strCheckDate, "mm/dd/yyyy")
dblTotal = 0

For i = lngStart To lngEnd
Set wks = wkb.Worksheets(i)
If (Format(wks.Range(strAddressCheck).Value, "mm/dd/yyyy") =
strCheckDate) Then
dblTotal = dblTotal + wks.Range(strAddressSum).Value
End If
Next i


SumSalaries = dblTotal

Set wkb = Nothing

End Function


Public Function ExcelSheetExists(wkb As Workbook, strSheet As String) As
Boolean
Dim wks As Worksheet

On Error GoTo ErrHandler

Set wks = wkb.Worksheets(strSheet)
ExcelSheetExists = True

Exit Function
ErrHandler:
ExcelSheetExists = False
End Function

 

[Tracy]

Thank you for the information. I am afraid I have never done anything in
VBA. I have tried to figure out how to insert the code but have gotten side
tracked with other work. I hope to have more time tomorrw. I will take any
help you can offer though.

Thank you,

Tracy

 

[Bill Pfister]

-Go to the Visual Basic Editor (Excel main menu / Tools / Macros / Visual
Basic Editor
-Press Ctrl-R to bring up the Project Explorer (it may already be up)
-Find your workbook and click on it in the Project Explorer
-Right-click and choose Insert / Module
-Copy-paste the "Put this code into a module in VBA" code into the window
-Close the editor
-Save your workbook

 

[Tracy]

Goodmorning,
I had a chance to do what you said yesterday. I copied and pasted the VBS
into the Visual Basic Editor and I put the formula into the cell that I
wanted to sum the totals in. I am getting an error message "Campile error:
Expeted: identifier". Can you help me with this?

Thank you,

Tracy

 

[Bill Pfister]

When you get the "Compile Error" message, do you have the option to "Debug"
or is there a specific line that is highlighted?
If not, go to the Visual Basic Editor (Alt-F11 from Excel) and select "Debug
/ Compile VBA Project..." from the menus, and see if it highlights anything.

 

[Tracy]

I did what you asked. I still got the same error message and it did not
highlight anything.

Tracy

 

[Bill Pfister]

Tracy, I think I finally found the problem. Delete the previous code from
the module, and replace it with this. Let me know if it works.

Bill




Public Function SumSalaries(strCheckDate As String, _
strSheetStart As String, strSheetEnd As String, _
strAddressCheck As String, strAddressSum As String) _
As Double
Application.Volatile

Dim wkb As Workbook
Dim wks As Worksheet
Dim lngStart As Long
Dim lngEnd As Long
Dim i As Long
Dim dblTotal As Long

Set wkb = ThisWorkbook

If (Not (ExcelSheetExists(wkb, strSheetStart))) Then
Call MsgBox("Sheet (" & strSheetStart & ") does not exist!")
Exit Function
End If

If (Not (ExcelSheetExists(wkb, strSheetEnd))) Then
Call MsgBox("Sheet (" & strSheetEnd & ") does not exist!")
Exit Function
End If

lngStart = wkb.Worksheets(strSheetStart).Index
lngEnd = wkb.Worksheets(strSheetEnd).Index

strCheckDate = Format(strCheckDate, "mm/dd/yyyy")
dblTotal = 0

For i = lngStart To lngEnd
Set wks = wkb.Worksheets(i)
If (Format(wks.Range(strAddressCheck).Value, "mm/dd/yyyy") _
= strCheckDate) Then
dblTotal = dblTotal + wks.Range(strAddressSum).Value
End If
Next i


SumSalaries = dblTotal

Set wkb = Nothing

End Function


Public Function ExcelSheetExists(wkb As Workbook, _
strSheet As String) As Boolean
Dim wks As Worksheet

On Error GoTo ErrHandler

Set wks = wkb.Worksheets(strSheet)
ExcelSheetExists = True

Exit Function
ErrHandler:
ExcelSheetExists = False
End Function