Results 1 to 8 of 8
Thread: excel problem (2002)

20031120, 06:31 #1
 Join Date
 Nov 2003
 Location
 Nebraska
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20031120, 07:28 #2
 Join Date
 Feb 2001
 Location
 Weert, Limburg, Netherlands
 Posts
 4,812
 Thanks
 0
 Thanked 0 Times in 0 Posts
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))Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20031120, 07:37 #3
 Join Date
 Nov 2003
 Location
 Nebraska
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: excel problem (2002)
Thanks so much!!

20031120, 17:27 #4
 Join Date
 Nov 2003
 Location
 Nebraska
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
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?

20031123, 13:52 #5
 Join Date
 Jan 2002
 Location
 The Hague, Netherlands
 Posts
 283
 Thanks
 0
 Thanked 0 Times in 0 Posts
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$11,0,IF(ISNA(D$1),1,D$1C$1),1)=$A3),(OFFSET(Sheet2!$B$11,C$11,0,IF(ISNA(D$1),1,D$1C$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.Microsoft MVP  Excel

20031127, 03:21 #6
 Join Date
 Nov 2003
 Location
 Nebraska
 Posts
 9
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: excel problem (2002)
Steve, I tried to add your suggestions to my program but I am getting runtime errors.

20031127, 11:32 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: excel problem (2002)
<P ID="edit" class=small>(Edited by sdckapr on 27Nov03 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
Add this to a module:
<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

20031127, 11:35 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
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