Results 1 to 8 of 8
  1. #1
    New Lounger
    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.

  2. #2
    Platinum Lounger
    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.jkp-ads.com
    Professional Office Developers Association

  3. #3
    New Lounger
    Join Date
    Nov 2003
    Location
    Nebraska
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: excel problem (2002)

    Thanks so much!!

  4. #4
    New Lounger
    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?

  5. #5
    3 Star Lounger
    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$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.
    Microsoft MVP - Excel

  6. #6
    New Lounger
    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 run-time errors.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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 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

    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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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

Posting Permissions

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