MrExcel.com - Your One Stop for Excel Tips & Solutions

I need Help with a formula!

Posted by Andy Dibble on February 02, 2002 6:02 AM

I have a reference code in one cell, followed by a description and finally a calculated cell telling me how many I have in stock.

The issue I have is each reference code as sub codes allocated to the reference code. For example; reference code ABC1234 may have say 6 sub-codes allocated to the ABC1234 code, thus making up once complete item. Each sub code has a description, a total fixed qty amount, total number available, a total number allocated to customers and finally a total number available.

I have VLOOKUP database listing all my sub codes with the cell information as described above, and all my reference codes on the key sheet.

The issues I have is I want to know haw many reference code Items I have available.

My dbase looks similar to this:
Reference Sub code Qty Item In Stock Sold Avail
ABC1234 HKC0070 1 legs 528 301 227
ABC1234 HKC4201 1 carcase 26 0 26
ABC1234 HKD5100 1 drawer 4 0 4
ABC1234 HKD5104 1 door 4 0 4
ABC1234 HKE8199 1 hinge 328 76 252
ABC1234 HYH2130 1 Drw box 6 0 6
ABC1234 HYJ5298 2 handles 57 0 57

Each reference code is made up of b/down codes. For example the ABC1234 comprises of 7 sub-codes with fixed qty’s. Therefore in this sample I have only a total of X4 items avail for sale even though some of the other items have greater availability, I am only governed on the minimum qty’s available compared to the fixed sub-code qty’s

My reference page looks like this:
Reference Avail
CWY1100 Conway 300 STD d/l base ?

I need the formula that fits in the key sheet Avail cell? Any help I can get would be well appreciated.

Regards
Andy
andycaps@emirates.net.ae

If the above format does not read well I can resend to you directly to your e-mail in a better format

Posted by Barrie Davidson on February 02, 2002 6:50 AM

Andy, I your example table above would be in the range A1:G8 if I understand it correctly. To determine the number you are after (4), I put "ABC1234" in cell A12 and the following formula in cell B12.

=MIN((A2:A8=A12)*(E2:E8))

Note that this is an array entry (must be entered using CTRL+SHIFT+ENTER, not ENTER). Adjust the formula as required.

Regards,
Barrie
Barrie Davidson

Posted by Ed Acosta on February 02, 2002 7:36 AM

If you are looking for a formula to reference your inventory sheet this formula will allow you to reference your inventory sheet instead of using the Vlookup function.

MIN(INDEX(Inventory!A:G,MATCH(A2,Inventory!A:A,0),7):INDEX(Inventory!A:G,(COUNTIF(Inventory!A:A,A2)+MATCH(A2,Inventory!A:A,0)-1),7))

I will E-mail you the sample spreadsheet so you can see how it works.