Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts

    RECORDING STARTING AND ENDING CONTROL RECEIPT #s

    I downloaded into EXCEL the attached worksheet. I color coded the 4 different CLASSIFICATION sections.

    I am trying to record the Starting Receipt NO. and the Ending Receipt NO. for each of the CLASSIFICATIONS.

    I would like to do this with formulas and not a macro.

    As you see in Cells J1 THROUGH N5, I have a small schedule set up. This will be the Information needed for other worksheets.

    Is there a way this can be achieve? If so, How could it be achieved?

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    In K2, try: =MIN(IF(0<(($J2=$A$2:$A$35)*($G$2:$G$35)),(($J2=$A $2:$A$35)*($G$2:$G$35)))) as an array formula (CTRL+Shift+Enter)

    and in L2 try: =MAX(($J2=$A$2:$A$35)*($G$2:$G$35)) also, as an array formula.

    Fill down.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    MNN,

    Here's another solution that doesn't use Array Formulas.

    K3 = =VLOOKUP($J2,MyTable,7,FALSE)
    L3 = =OFFSET($G$1,MATCH($K2,$G$2:$G34,0)+$M2-1,0)
    M3 = =COUNTIF($A1:$A34,$J2)0
    Fill down.

    Note: MyTable = $A$2:$G$35
    TableXLS.JPG

    HTH

    MNN - RCPT # TEST.xlsx
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    RG

    ..you meant to use K2, L2, M2 in your formulas above, NOT K3, L3, M3!
    ..but your attached file was correct.

    zeddy's version:
    and another version..
    K2 =INDEX(G:G,MATCH(J2,A:A,0))
    L2 =OFFSET($G$1,MATCH(J2,A:A,0)+M2-2,0)
    M2 =COUNTIF(A:A,J2)
    ..so you don't need to define myTable etc.


    zeddy
    Attached Files Attached Files

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Zeddy,

    Right you are! Thanks for catching my boo boo.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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