Forum Discussion

Steve Brand's avatar
Steve Brand
Copper Contributor
May 23, 2019

Formula Help

Good day to all. I want to create a small Stock Management System. The idea is to have a master sheet with "Item", "Description", "Cat1", "UoM", "Cost", "Sub Total", "VAT" and "TOTAL" in it. I do n...
excel
Formulas and Functions
Twifoo's avatar
Twifoo
Silver Contributor
May 24, 2019
I suggest that your opening inventory must be included in the In Sheet, Thereafter, On Hand will simply be In - Out.
Steve Brand's avatar
Steve Brand
Copper Contributor
May 28, 2019

Twifoo 

Hi, See the problem is that I can select the item over a number of lines, I can book out the same item on line 4, 11, 26 and 37.

The issue is that on each line of the same item being booked out the SOH must update and be "live".

I understand what you are saying and it will work if I book that item out on one line only. I can as well actually capture my opening stock under the "In" tab, this will then make my SOH hat I have. But as I mentioned, this does not resolve the problem.

Check my comment on the test sheet I sent. The reason there can be multiple lines with the same unit is that I have 5 plumbers, 5 electricians and for each job they book out stock. thus I type in the reference for where the job is. I cant update the reference every time I book out on the same line.

 

Regards

Steve

  • Twifoo's avatar
    Twifoo
    Silver Contributor
    May 28, 2019

    Steve Brand 

    As I stated earlier, On Hand would simply be In - Out, with this formula: 

    =SUMIF(InSKU,A2,InQty)-SUMIF(OutSKU,A2,OutQty)

    The cumulative quantity on hand is determined with this formula: 

    =SUMIF(InSKU,B2,InQty)-SUMIF(OutCumSKU,B2,OutCumQty)

    Test the foregoing formulas in the attached version of your file and inform me of your thoughts thereon. 

    • Steve Brand's avatar
      Steve Brand
      Copper Contributor
      May 28, 2019

      Twifoo 

      Hi,

      Thank you, actually figured that SUMIF out yesterday after sending you the request for assistance.

      All sorted now and thank you for your assistance as well.

      • Twifoo's avatar
        Twifoo
        Silver Contributor
        May 28, 2019
        Take note that I included the opening stock as part of the In sheet for the formula to work. Also, the Out sheet includes a cumulative formula for stock on hand as entries are added thereto.

Resources