Inventory tracking

Hello,
I am trying to create a spreadsheet which tracks inventory. So far I have set up the items and quantities and now I am trying to create a formula to do this process. I plan to scan the barcode for the item and have Excel recognize the corresponding barcode and then subtract it from the original quantity.
This formula doesn't work but it shows what I am trying to achieve:
ItemaaaaaaaQuantityaaaaaaBarcodeaaaaaaaRemainder
XYZaaaaaaaa5aaaaaaaaa130-001aaaaaaaaaa =B2-(whenscanned=C2)then1

I'm not entirely sure this is even possible.
It would be great if anyone has done something like this before or someone just up for a challenge to find a way to do this.
I am going to keep trying to solve this and I'll post again if I find a solution.
Thank you for your help

Hi menty

You say you are "trying to create a spreadsheet which tracks inventory". But the example you show does not "track" the inventory, it merely shows the balance. I believe that you will have issues, trying to find out what has gone wrong when it (invariably) does go wrong.

I have suggested a system that will enable you to track all the ins and outs which will give a balance, but also allow you to track back.

The system relies on you adding the stock before removing it. (Logical, but some people try to sell before they have purchased!). The reason for the #N/A in B5 and C5 is that the additions tab is designed to get the Stock reference and Description from any relevant entry above. If there is no relevant entry, it is necessary to overtype the formula. (tip - copy the formula down to the line below for future reference!)

Your barcode may be scanned in to the Deletions sheet which will update the Inventory sheet.

I have (for no particular reason) limited the stock transactions to 90,000; this may be increased to a maximum of 1,048,576.

I hope that you will find the file useful.

Regards
Alastair