Inventory Sorting sheet need help.xlsm
Using replies to previous threads in this forum, I've compiled the attached excel sheet that's going to assist me in physical sorting of a bunch of items to determine which carton the item should go to "Carton A" or "Carton B".
Here's what the excel program does so far -
1. Select cell A4 and press the click the button on a barcode scanner attached to the computer. If the barcode number read is under column C "Barcode Number" - list which has been previously entered, then the column B "QTY" will get updated by 1.
2. Similarly if I want to reduce the quantity, then I select cell C4 and take a barcode reading. Likewise if I want to find a barcode, I slect cell E4 and take a reading and the found cell under column C will have a box.
3. Column N and O contain the allocation instructions I have previously received about how many pieces need to go into carton A and B. This data, like Column C has been entered previously.
4. Taking item - Toy Car - as an example, the allocation instructions columns N and O tell me that I should put 6 Toy cars in Carton A and 12 in Carton B.
So here it begins....
Round 1: Using scanner take a barcode reading of toy Car
B8 shows 1/ I8 shows 1/ J9 shows -
Round 6: B8 shows 6/ I8 shows 6/ J9 shows -
Round 7: B8 shows 7/ I8 shows 6/ J9 shows 1
Round 8: B8 shows 8/ I8 shows 6/ J9 shows 2
Round 18: B8 shows 18/ I8 shows 6/ J8 shows 12
Round 19: B8 shows 18/ I8 shows 6/ J8 shows 12/ K8 shows 1 "excess"
The program does this already so far.
Here's what I'm not able to do, and request someone's assistance. Many thanks in advance:
1. If a cell in column B <= a cell in the same row in column N, then I want a pop-up message that says "Put item in Carton A". The the user has to click OK button only once and the msg box should disappear.
2. If a cell in column B > a cell in the same row in column N, then I want a pop-up message that says "Put item in Carton B". The user has to click OK button only once and the msg box should disappear.
3. As sooon as a cell in column J reaches its maximum limit (prescribed by column O), then I'd like a pop-up message that says "Allocated pieces for Carton B has reached its limit. Anything more means that, physically, excess has been received than anticipated. Keep aside in a separate carton"
4. Lastly, please make note of the fact that I will be adding many more rows so the program should account for additional rows being added.
I've attached the xlsm file in this thread