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 qtys. 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 qtys available compared to the fixed sub-code qtys
My reference page looks like this:
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.
If the above format does not read well I can resend to you directly to your e-mail in a better format