excel problem (2002)

Topic

New Reply

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.

Reply | Quote

Replies

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))

Reply | Quote

Thanks so much!!

Reply | Quote

Thanks so much!!

Reply | Quote

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?

Reply | Quote

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:))

See the attachment.

Reply | Quote

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:))

See the attachment.

Reply | Quote

(Edited by sdckapr on 27-Nov-03 06:32. Correct syntax error)

If you add this to the workbook object:

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

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:

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

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 to calculate any workbook/sheet when this “slow” one is open, it will also calculate that workbook too.

Steve

Reply | Quote

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

Reply | Quote

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:

 .Calculation = xlCalculationManual

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

Steve

Reply | Quote

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:

 .Calculation = xlCalculationManual

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

Steve

Reply | Quote

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

Reply | Quote

(Edited by sdckapr on 27-Nov-03 06:32. Correct syntax error)

If you add this to the workbook object:

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

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:

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

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 to calculate any workbook/sheet when this “slow” one is open, it will also calculate that workbook too.

Steve

Reply | Quote

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?

Reply | Quote

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))

Reply | Quote