# Thread: RECORDING STARTING AND ENDING CONTROL RECEIPT #s

1. ## 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?

2. 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. 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

4. 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

5. Zeddy,

Right you are! Thanks for catching my boo boo.

#### Posting Permissions

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