Search for like values in several worksheets and sum them in a totals worksheet

[wllmodom]

I have a workbook that contains 53 sheets (one for every week of the year and a Totals sheet) where I am downloading production data each week by operator and want to keep a running total in my totals sheet. There are a varying number of operators each week. I want to look for an operator and sum their production in the totals sheet. I tried sumif() but could not figure out how to do multiple sheets.

 

[Peter_SSs]

Welcome to the MrExcel board!

To decide if something might be suitable for you, we would need to know more about the layout of the weekly sheets and where we might find an operator and their production total for that week.
Similarly, we would need to know about the layout of the Totals sheet including where the operator names are and where the totals should go.

It may be that a macro solution might be best for you and, if that turns out to be the case, would that be acceptable?

 

[wllmodom]

I am self taught so there are large gaps in my excel knowledge. I have not used macros before, but I am willing to learn how to use them. As for my layout the third column of every worksheet contains the operator ID which is unique to them and the fourth column contains their production numbers. So I am trying to look in column C in 52 worksheets and find on operator ID and pull their production numbers from column D and then sum them up in the totals worksheet. I am thinking I have to have a formula for each operator Id, but don't know. The totals sheet is a master list of all the operator IDs and is linked to an access database. The issue is that there are varying operators each week, some are producing every week and some are producing one week but not another. This data is downloaded and contains 100 to 150 operators.

 

[Peter_SSs]

A few more questions
1. What about the layout of the Totals sheet? where are the operator ID? Where will the results go?

2. Am I right in thinking that a particular operator ID can never appear more than once on a weekly sheet?

3. I probably won't need this information but while I'm asking, what are the names of the weekly sheets like?

 

[wllmodom]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Operator[/TD]
[TD]Production Totals[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]A13564[/TD]
[TD]52.2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]GODELM[/TD]
[TD]16.5[/TD]
[/TR]
</tbody>[/TABLE]
The totals sheet is named Production Totals and is at the far left. Each weekly sheet is in order and named for the week it represents: Week1;Week2;Week3;and so on. No spacing between the week and the number.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Manufacturing Process Group[/TD]
[TD]Calendar Year/Week[/TD]
[TD]Operator[/TD]
[TD]Matl Produced, RUOM[/TD]
[/TR]
[TR]
[TD]Widget making[/TD]
[TD]01/01/17[/TD]
[TD]GODELM[/TD]
[TD]1.2[/TD]
[/TR]
[TR]
[TD]Mallet making[/TD]
[TD]01/01/17[/TD]
[TD]A13564[/TD]
[TD]4.5[/TD]
[/TR]
</tbody>[/TABLE]
The weekly sheets are made up like the diagram and operators appear on different rows, but only appear a maximum of once per sheet, but may not appear on some sheets.

 

[wllmodom]

I do sort the downloaded file, but the problem I run into is that if an operator was out a week everyone shifts up and it throws my totals off if I try to sum cells across the sheets. I think there is a better way to import my download, but can not figure it out. Trying to find a good tutorial to learn macros and VBA, but have not found one yet.

 

[Peter_SSs]

Here is one way, using a user-defined function (UDF). To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

If you subsequently change the layout of the weekly sheets and the data of interest is no longer in column C & D, you can just alter it in the 'Const' lines below.

Function SumProduction(sOperator As String) As Double
  Dim ws As Worksheet
  Dim Found As Range
  Dim oSet As Long
  
  Const WeeklyIDCol As Long = 3   '<- Column C (Operator)
  Const WeeklyProdCol As Long = 4 '<- Column D (Production)
  
  Application.Volatile
  oSet = WeeklyProdCol - WeeklyIDCol
  For Each ws In Worksheets
    If LCase(ws.Name) Like "week*" Then
      Set Found = ws.Columns(WeeklyIDCol).Find(What:=sOperator, LookAt:=xlWhole)
      If Not Found Is Nothing Then SumProduction = SumProduction + Found.Offset(, oSet).Value
    End If
  Next ws
End Function

Book1
	A	B	C
1	ID	Operator	Production Totals
2	1	A123	12.6
3	2	B999	0
4	3	D456	13
Production Totals

Cell Formulas
Range	Formula
C2	=SumProduction(B2)

 

[wllmodom]

Thank you. It works great.

 

[Peter_SSs]

Thank you. It works great.

Great! Thanks for letting us know.