Adding and Subtracting (Quanity) Using 2 different barcodes

[rage 0wnz]

My Excel Spreadsheet has two barcodes one for Barcode for Out/Invoice (-). The other for products going inside inventory (+). I'm trying to make it so when I scan inside the "Scanning Barcodes" Sheet it will either add or subtract from the Amount left in inventory column.

Can someone please help me, I've been working on this for a while now, but I'm in experienced and I need help:P

If you look into the spread sheet you will see what I mean:P

[Sam Capricci]

could you explain a bit more? your invoice sheet has info in it, the database sheet also has info but the scanning barcodes sheet is blank. I can't tell what it is you want done? The only thing I come up with is if you enter the barcode from the database sheet column B that you want the invoice amount added(?) and if you enter the barcode from the out/invoice column A you want it to subtract? Maybe redo your attached sheet with some hand entered sample results would make it clearer.

[rage 0wnz]

could you explain a bit more? your invoice sheet has info in it, the database sheet also has info but the scanning barcodes sheet is blank. I can't tell what it is you want done? The only thing I come up with is if you enter the barcode from the database sheet column B that you want the invoice amount added(?) and if you enter the barcode from the out/invoice column A you want it to subtract? Maybe redo your attached sheet with some hand entered sample results would make it clearer.

I need it to add and subtract from the quantity. So like, if I scan in the scanning sheet it should add if I scan the barcode of the item. But if I scan the out barcode of the item it should subtract it.

[rage 0wnz]

I need it to add and subtract from the quantity. So like, if I scan in the scanning sheet it should add if I scan the barcode of the item. But if I scan the out barcode of the item it should subtract it.

The invoice is fine. Its the database sheet and the scanning sheet. I just want to get an amount left in the inventory every time I scan something in or out.

[Sam Capricci]

I am not sure if this is what you want or not. This may give you the answer... =IFERROR(IF(LEFT(A5,2)="in",VLOOKUP(A5,Database!B:C,2,FALSE)+(VLOOKUP(A5,Invoice!A:B,2,FALSE)),VLOOKUP(A5,Database!A:C,3,FALSE)-VLOOKUP(A5,Invoice!A:B,2,FALSE)),"")
The assumption is that if you enter in a cell in A5 of the scanning barcode sheet with a value that is in column B of your database sheet it will take the value in column C and subtract the corresponding value found in the invoice sheet from it.

[rage 0wnz]

I am not sure if this is what you want or not. This may give you the answer... =IFERROR(IF(LEFT(A5,2)="in",VLOOKUP(A5,Database!B:C,2,FALSE)+(VLOOKUP(A5,Invoice!A:B,2,FALSE)),VLOOKUP(A5,Database!A:C,3,FALSE)-VLOOKUP(A5,Invoice!A:B,2,FALSE)),"")
The assumption is that if you enter in a cell in A5 of the scanning barcode sheet with a value that is in column B of your database sheet it will take the value in column C and subtract the corresponding value found in the invoice sheet from it.

Where would I have to place that code?

[Sam Capricci]

In the scanning barcodes sheet.

EDIT: this might also work... =IFERROR(VLOOKUP(A4,Database!B:C,2,FALSE)+(VLOOKUP(A4,Invoice!A:B,2,FALSE)),VLOOKUP(A4,Database!A:C,3,FALSE)-VLOOKUP(A4,Invoice!A:B,2,FALSE))
again, in the scanning barcodes sheet.

[rage 0wnz]

In the scanning barcodes sheet.

Alright thanks. Im going to see if it works btw thx for the help

[Sam Capricci]

this is what I think you want but for me it is still not clear.

[rage 0wnz]

this is what I think you want but for me it is still not clear.

Let me give you an example.

If I scan a code from Column B, the code will go to the scanning sheet. Column B is the (in) so it will add to Column C in the database Sheet.
If I scan a code from Column A, the code will go to the scanning sheet. Column A is the (out) so it will subtract from Column C in the database Sheet.
^If you have any questions let me know.

P.S. The invoice should be affected in any way because I will be removing rows after I print out each invoice.

[rage 0wnz]

Would Count Cells Based on Background Color in Excel work?

[rage 0wnz]

Thanks man you have me a good Idea, I separated the invoice and the inventory to have a good view of things. And then I used the =countif function that counted the in and out Rep++