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 29, 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.

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

    TwifooHi and thank you for your reply.

     

    I fail to see how that would help me.

    The main thing is that the same item can be booked out on different lines.

    How would that continue to keep the SOH "live"?

     

    Any chance of sending an example?

     

    Thank you

     

    Kind Regards

    Steve

    Twifoo wrote:
    I suggest that your opening inventory must be included in the In Sheet, Thereafter, On Hand will simply be In - Out.

     

Resources