AB or BA

F

fail2excel

Need a way of selecting the smallest stock glass size needed. Columns A and B
represent the stock glass sizes (A4:B4=16x20). Here is the trick my work
worder could be either 20x16 or 16x20 or any size at all. So I need it to be
able compare both dimensions. I also have macro buttons for each glass type
that my customer might select so if I press the RG button I want it to run
through the sizes, selecting the smallest possible piece
"Sheet 1"
E15 E16 are where the work order dimensions are

"Sheet 2"
A B C E G I K

RG: CC: MG: CN: RN:
4 16 20 $1.73 $3.16 $23.00 $6.43 $3.47
5 18 24 $4.28 $4.69
6 20 24 $4.85 $5.32
7 22 28 $3.32 $6.06 $12.33 $6.65
8 24 30 $3.99 $7.28 $7.98
9 24 36 $4.99 $9.09 $50.50 $18.49 $9.98
10 26 32 $4.43 $8.08 $8.87
11 32 40 $6.65 $12.13 $24.65 $13.30
12 36 48 $9.97 $18.19 $101.00 $36.98 $19.95
13 40 60 $16.53 $44.95 $167.00 $29.60
 
B

Bernie Deitrick

Array enter (enter using Ctrl-Shift-Enter) the formula

=MIN(IF((A4:A13>=MIN(E15:E16))*(B4:B13>=MAX(E15:E16)),ROW(A4:A13)))

The formula will return the row number of your smallest stock size.

HTH,
Bernie
MS Excel MVP
 
Top