Avoiding double counting in a inventory tracking sheet

Taylor_shephard

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?

Ryan Sides

@Taylor_shephard

Hi Taylor! I'm not following the vaccine count.

If you guys send 4, they count 4.

They use 2, they have 2 left.

You ship 6 vaccines, they count 6. They should be counting 8 at this time (2 leftover and the 6 you shipped, right?)

What's the problem you're looking to solve?

Taylor_shephard

So the above photo loosely represents the example I mentioned above. This time, instead of sending 6 the second time, we sent 4. I'm testing things out with the Moderna Monovalent row.

The formula in "Quantity of Vials - Reported from Inventory" is a cross reference SUMIF formula, adding the submissions from our inventory tracking form for the given vaccine IF the community being submitted is "Aklavik". So right now, when the column tracks the amount sent it doesn't factor in the 2 vaccines that got used previously. It just adds the new inventory to the sheet, and ad the previous inventory from the other sheet. Instead of showing 8 vaccines total, it shows 10 instead. leaving us with 2 missing vaccines on the shipping side of things.

Ryan Sides

@Taylor_shephard Are you having them input how many vials they used? Or are they just submitting how many they have in inventory at a specific time?

Taylor_shephard

It would be both. They will report the vials used and their inventory on hand weekly, or in some cases whenever they do a clinic and offer the services.

Ryan Sides

@Taylor_shephard The numbers in and out aren't adding up for me. If you want, shoot me an email at ryan@workflowcreative.com and we can jump on zoom to get this done for you.