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

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" wrote:

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

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" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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

-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" wrote:

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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