Hello!
I am trying to create a inventory sheet tracking the inventory of vaccines across 30 different regional health centers. The idea is to have 3 forms feeding information into a master sheet that displays this info on a community by community level. Right now, one form is for our central office to track shipments, another form is for the community health center to track their stock, and a final form to track usage of waste. a goal of the sheet is to track inconsistencies between stock that's actually been shipped, and stock that's actually being reported at the community center.
I ran into an issue where this form would collect data, and duplicate left overs from a previous shipment when filling out a new form. Example: We ship 4 vaccines to a center, they count 4 in their inventory, they use 2 vaccines, and have an actual inventory of 2 remaining after use. We then ship another 6 vaccines, and the community center counts a total of 6 vaccines now (4 new ones, and 2 from the previous batch), they don's use any vaccines for the period, and their remaining count should be 8 total, but it actually gets inflated due to 10 because 2 of the vaccines get double counted in the inventory form filled about by the health center.
Does this make sense to anyone? And if so, can you lend a hand?
