sumif issue

**redneck joe** · 11-18-2006, 12:33 PM

Spent last couple hours searching, no luck. (did find couple other solutions to unrelated problems I needed help with, though!)

Have:

400 jobs per day
assigned to 200 people per day
in 12 different cities
each city has it's own tab
each day has different set of technicians working these jobs
each job has different assigned point value.

I need to pull to a summary tab:

the technician #
then
sum the total of assigned points to that technician
then
sum the completed points by that technician.

I've done this by creating a list of tech #'s for the formula to read first, but in this case the list of tech #'s is ever changing and way too dynamic to keep updating every day, so I would like it to read all the tech #'s working on that day and create it's own list.

If I can get help with that, I can do the second and third part as I have done in the past.

One last oddity, a technician can be working in two different cities on a given day so his tech # would be on two or more tabs.

I have an example workbook - if needed I (think) I can attach.

**mrice** · 11-18-2006, 02:35 PM

You might be better doing this one by macro which searches all the tabs and compiles the list on the summary tab depending upon what it finds. I guess that you will need to be careful that any one technician is given more than one ID on the separate tabs - even a slight difference in a way that the name was written would be enough to produce confusing results.

**redneck joe** · 11-18-2006, 03:19 PM

OK... I've recorded a macro just for fun to do some mouse clicks, but have not done anything else, like actually writing code.

Where to start that learning process?

**mrice** · 11-18-2006, 04:56 PM

You'll need something more that that you can record if you want to loop through all the sheets. If you want to find out more, have a look at the for...next loop in VBA help. (Alt F11 then help).

Are you able to attach a copy of the workbook so that we can see the detail of what you are trying to achieve? - it should be then possible to give more useful advice.

**redneck joe** · 11-18-2006, 05:00 PM

Yes sir.

I have this small workbook as example.

First two tabs are two cities with the end of day info that I will be using.

Summary tab has headers with a description of what I need them to have in the columns.

Of course, this will be expanded to 12 tabs, but I can handle that part.

**Maistrye** · 11-18-2006, 05:28 PM

You can try this macro.

Basically, it loops through all your sheets to gather data (excluding the one specified in the SUMMARY_NAME constant.

It then puts the summary information in the SUMMARY_NAME sheet.

It makes a couple of assumptions:

First, that your employee ids are all numeric and below 50,000. You can adjust this upward, but it won't work with non-numeric employee ids.

Second, that your description for being done is "complete".

Third, the sheet you want to skip is "summary".

Fourth, the columns listed in the example are the columns that you are actually using.

Plus, some other little things in comments.

Please Login or Register  to view this content.

Scott

**redneck joe** · 11-18-2006, 05:37 PM

your assumptions are all correct - could you possibly attach the workbook with the macro in it? I'm much better at tearing them down and figuring it out than I am at building from scratch...

**Maistrye** · 11-18-2006, 05:59 PM

Ok, here you go.

Just to ramble on, to use the code above, you're probably better to just copy it into your spreadsheet with all your data as opposed to copying all the data, although that is just a personal opinion. (I would also make a backup, in case things get screwed up. Full of confidence, aren't I?)

All you have to do is go to VBA (Alt-F11), on the side, select the Workbook you want to put the macro in. Then choose Insert -> Module (Alt I - M), then paste the code in that module.

Then you can run it with Alt-F8 and choose the procedure from the list.

Scott

**redneck joe** · 11-18-2006, 06:52 PM

Originally Posted by Maistrye

(I would also make a backup, in case things get screwed up. Full of confidence, aren't I?)

preachin' to the choir...
My work book is actual fairly large with lots of other stuff. I want to put the macro in and start plyaing with the code to see how it works. Kinda like tearing apart a motor and putting back together (hopefully) correctly.

All you have to do is go to VBA (Alt-F11), on the side, select the Workbook you want to put the macro in. Then choose Insert -> Module (Alt I - M), then paste the code in that module.

Then you can run it with Alt-F8 and choose the procedure from the list.

Went over my head pretty quick.

got steps #1, #2, and part of #3 but when I get to the M part, the option is greyed out. Do I need to create a clean module somehow in the actual workbook I'm trying to paste in? If I can get to that point, I'll try the alt F8 part...

**duane** · 11-18-2006, 06:54 PM

another approach to gather all the tech #'s - not required to be numeric

Sub macro1()
Dim sht As Worksheet
Dim tech(100) As String
i = 0
For Each sht In ActiveWorkbook.Worksheets
sht.Activate
If sht.Name = "summary" Then GoTo done
'If sht = "Summary" Then GoTo done
firstrow = sht.Cells(1, 1).End(xlDown).Row
lastrow = sht.Cells(65000, 1).End(xlUp).Row
If lastrow = firstrow Then firstrow = 1
For j = firstrow To lastrow
If i = 0 Then GoTo firstone
For k = 1 To i
If sht.Cells(j, 1) = tech(k) Then GoTo nextj
Next k
firstone:
i = i + 1
tech(i) = sht.Cells(j, 1).Value
nextj:
Next j
Next sht
done:
Sheets("Summary").Select
For m = 1 To i
Cells(m + 1, 1) = tech(m)
Next m
End Sub

**Maistrye** · 11-18-2006, 07:26 PM

Playing with it is very understandable.

Here's one site, not much, but a little bit on what I was trying to show:

http://www.contextures.com/xlvba01.html

Here's another with a couple more screenshots. (Only the first bit applies)

http://www.fontstuff.com/vba/vbatut03.htm

I guess the key point is that when you hit Alt-F11, you have to select the name of the spreadsheet on the left side listing. (like they show in the screenshots on these sites)

(It's so much easier to show someone in person... and once you see it, it's fairly easy. The explanation in words is the hard part.)

With the copy I attached, you should be able to run it, provided your Macro Security is set to Medium. (Low is baddddd.... High won't allow you to run macros in general.) (Tools -> Options ... choose the Security tab, click the Macro Security button, change it to Medium.)

Scott