2 replies
by jeninems Accepted solution
by locolobo Accepted solution
The simplest way I can figure this one without writing a macro would be to use the SUMIF function. My method would require using an initial Qty on the inventory page. The formula would look something like this:
=B2-SUMIF(Order!$A$5:$B$14,A2,Order!$B$5:$B$14)
Where B2 is the initial Qty
Order!A5:B14 is the range containing the product id and Qty ordered. Column A would have the product id and B the Qty.
A2 contains the product id you want inventoried.
Order!B5:B14 is the summary range. You can look that up in the help file for excel.
The formula above doesn't take into account supplies received. You have 2 ways to do that. First adjust the init each time you receive supplies. Second add a Supply Receiving page and change the formula to look something like:
=B2+SUMIF(SupplyRec!$A$5:$B$14,A2,SupplyRec!$B$5:$B$14)-SUMIF(Order!$A$5:$B$14,A2,Order!$B$5:$B$14)
Hope that helps.
edited to fix my booboo
This discussion is closed to new replies.
Trending Software posts
Sort by Trending