Cycle counts from linked sheets: everchanging quantities

[tubbycricket]

Hey everyone, this is my first time here, I just had a question in my
mind and wanted to know if such a thing was possible or nonsensical:

The industry I work in, we use Excel to keep track of cycle counts we do
on our inventory on hand from time to time. When one of our guys gives
us a quantity, we enter that quantity into one cell while an adjacent
cell holds the original quantity on hand to compare it to.

For the time being, almost all the data is static and manually entered
from various sources, but mainly from our company ERP software.

What I want to do is this: I want to pull the quantity on hand for a
specific item from another Excel file that dynamically updates itself on
that parameter.

So, in essence, each time the sheet opens up, the quantity on hand would
change: one day the item can have 100 pieces, the next day it can drop
to 90 pieces if 10 pieces were picked. But in the interim, I had cycle
counted 95 pieces.

Now since there was a drop of 10 pieces, I would have to drop the cycle
count figure accordingly to 85 pieces from the original 95.

Question is: is there a way for Excel to recognize the drop in quantity
on hand during each update, and then apply that difference to other
cells as well?

Please let me know if you need more information, thanks.




--
tubbycricket