Results 1 to 6 of 6

Thread: index formula problem excel 2007

  1. #1

    index formula problem excel 2007



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Can anyone help sort out this problem

    Import C details deliveries to be made that day - sent in from another department.

    Workbook "Monday Run" details the drivers run sheet (602 RS) and his load (602OL). If the site ID appears in Import C then a "C" is put in 602 RS column D, and the site ID in 602 OL B28:B40. A vlookup then puts the details from Import C into the respective column in 602OL (D28 to O40).

    The problem occurs when a site has two or more orders for delivery that day - in this case both sites C062 and C070 have two deliveries each.

    Is there any way to amend the formula to pick up the second delivery from Import C for these sites??

    Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Perhaps with SUMPRODUCT? This adds up the corresponding values...

    e.g. in J28 or '602L' sheet:

    =SUMPRODUCT(('[IMPORT C.XLSX]C'!$G$8:$G$1000=B28)*('[IMPORT C.XLSX]C'!$I$8:$T$1000))

    adjust ranges to suit and copied down.

    Is that what you meant?


  3. #3
    Hi NBVC

    Thanks for the input - is there anyway this can be included in the formula already in B28:B40?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    I am not sure what you mean. The formula you have in B28:B40 gets the unique Store Numbers, and the formula I gave you sums up the corresponding values from the columns in the IMPORT workbook.

    FYI... you can revise the formula in B28 to:

    =IFERROR(INDEX('602 RS'!$A$5:$A$34,SMALL(IF('602 RS'!$D$5:$D$34="C",ROW('602 RS'!$A$5:$A$34)-ROW('602 RS'!$A$5)+1,ROW('602 RS'!$A$34)+1),ROWS($B$28:$B28)),1),"")

    so that you can copy it down without having to manually adjust the K factor for the SMALL function.


  5. #5
    Hi NBVC

    Unfortunately your sumproduct formula is not what I'm looking for. I've uploaded another copy showing the results I hope to get in B28:O42 with some of these results repeated in B11:O19.

    I hope this explains it better and you'll be able to help sort it out.
    Attached Files Attached Files

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    I am confused.

    Are you saying only 2 rows should appear in the results?

    If so, under what logic should these be the only two rows?

    You have not included the reference workbook for the numbers, so I am not sure what to expect in those columns.


Posting Permissions

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