I volunteer for an Athletic Youth Club that needs to keep track of their inventory of basketball and soccer uniforms.
Here are the worksheets and headers with problem data.
INVENTORY sheet
Column Headings:
The shorts (Shorts) are identified by size only. The quantities can be from 0 to the number of shorts in stock.
The formula in Column(Sold) is:
Note:
I use helper columns to identify unique jerseys and soccer sets, by size and jersey number, the quantities are always one (1). Above formula works as intended for jerseys and soccer sets (SocSet) using the Jersey# column.
Problem: Shorts-AL were bought twice a total of 24. Then sold 2 of them, balance should be 22, but using above formula, it is 20, the -2 is deducted from both purchases
The code I have now, works perfectly for sales of Jerseys and SocSet's, but not on generic products that don't have numbers on them also are bought in quantities and multiple times.
The code I have, is looking up "all" rows with "Shorts-AL" and "subtracting" the qty from all rows where Sales!H:H column corresponds to "Shorts-AL".
What I need to have is a code that will NOT subtract the qty from the Sales!H:H column from each row matching "Shorts-AL" in Inventory!G:G column, but DO subtract from only the first "Short-AL" that has in OnHand column a quantity equal to or more than the quantity sold. And if the quantity sold is "greater" than the quantity OnHand, subtract the balance from the "second" or Nth row matching "Shorts-AL".
I did try to use sumproduct, but I messed up real bad and could not figure out if I should use "-" or "+" or "*".
SALES sheet
All sales are registered here.
Column Headings:
I read that Access is better for tracking inventory, but could not find an appropriate template that keeps track of inventory by not only product type and size, but also jersey numbers on each uniform. Any help will be much appreciated. Have a great day!
Thank you all for reading my post, and if you can help that would be great!
JC
Bookmarks