1. ## excel problem (2002)

My first Excel spreadsheet has 1 row of dates & 1 column of names. My second spreadsheet is my information spreadsheet with serial numbers and also a column of dates and another column of names. I would like to be able to write some code in my first spreadsheet that would examine the name and the date of the second spreadsheet and give me the number of serial numbers worked on by any one person daily. I have attached an example.
Any suggestions would be greatly appreciated. I have been banging my head for quite a while.

2. ## Re: excel problem (2002)

Use this formula in the cells C3:L12 on sheet1:

=SUMPRODUCT((\$A3=Sheet2!\$F\$11:\$F\$26)*(Sheet2!\$A\$11 :\$A\$26=Sheet1!C\$2))

3. ## Re: excel problem (2002)

Thanks so much!!

4. ## Re: excel problem (2002)

I inserted your formula in a 3000 line database and the calculations take a while to do. Is there any way to only calculate every hour?

5. ## Re: excel problem (2002)

A different setup would allow for faster processing. It consists of restricting the required SumProduct formula to relevant subranges.
The prerequisite is that the data on Sheet2 be sorted on the Date column. Assuming that the sorting is done:

Switch to Sheet1.

Insert a row before the processing area on this sheet.

In C1 enter & copy across to the column of the last date plus 1:

=MATCH(C2,Sheet2!\$A\$11:INDEX(Sheet2!\$A:\$A,MATCH(9. 99999999999999E+307,Sheet2!\$A:\$A)),0)

In C3 enter & copy across then down:

=IF(ISNA(C\$1),"",SUMPRODUCT(--(OFFSET(Sheet2!\$F\$11,C\$1-1,0,IF(ISNA(D\$1),1,D\$1-C\$1),1)=\$A3),--(OFFSET(Sheet2!\$B\$11,C\$1-1,0,IF(ISNA(D\$1),1,D\$1-C\$1),1)<>"")))

BTW, you could also use the following formulas...

B3 on Sheet1:

=COUNTIF(Sheet2!\$F\$11:INDEX(Sheet2!F:F,MATCH(REPT( "z",255),Sheet2!F:F)),A3)

F1 on Sheet2:

=COUNTA(B11:INDEX(B:B,MATCH(REPT("z",255),B:[img]/forums/images/smilies/cool.gif[/img]))

See the attachment.

6. ## Re: excel problem (2002)

Steve, I tried to add your suggestions to my program but I am getting run-time errors.

7. ## Re: excel problem (2002)

<P ID="edit" class=small>(Edited by sdckapr on 27-Nov-03 06:32. Correct syntax error)</P>If you add this to the workbook object:

<pre>Private Sub Workbook_Open()
With Application
.Calculation = xlCalculationManual
.OnTime Now + TimeValue("01:00:00"), "CalcAll"
End With
End Sub</pre>

When you open the file it will turn calculation to manual and tell excel to run the macro "CalcAll" in 1 hour

<pre>Sub CalcAll()
With Application
.Calculate
.OnTime Now + TimeValue("01:00:00"), "CalcAll"
End With
End Sub</pre>

When the hour is up it will calculate and then tell excel to calc again in an hour, etc, etc.

Things to be aware of (these are true of XL97, not sure how different XL2000 is and if you have other options):
Manual calculation is not workbook dependent. No other workbooks/worksheets will calculate during this hour. When the hour comes all will be calculated.
If you hit <F9> to calculate any workbook/sheet when this "slow" one is open, it will also calculate that workbook too.

Steve

8. ## Re: excel problem (2002)

I do notice that you have the "workbook open" macro in a worksheet object. It belongs in the object named "thisworkbook".

Also I made a mistake in one of the lines, it should be:
<pre> .Calculation = xlCalculationManual</pre>

I apologize for the inconvenience. I have corrected it in the original post. Let us know if this fixes the problem.

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•