Formula Help

[Steve Brand]

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 not think there is a need to add a Qty Column in here as it will just confuse everything even more.

OPTION 1:

My 2nd sheet will be my "OUT" sheet, this is where I will use drop down menus to select the item. (This I can do).

My 3rd sheet will be a "Stock On Hand" sheet.

If I select the item from the drop down and in the cell next to it say the Qty issued out then after the work day is has concluded do a "Consolidation" under the DATA Tab it will combine everything. This I can do.

What I need is for this to update the SoH sheet once I have done the consolidation.

So if "Item A" was 1000 when the day started and over the day there was 249 booked out (from the Consolidation) then at the end when I run the Consolidate option it must automatically deduct 249 from 1000 making the start total the next morning 751.

OPTION 2:

If I have my MASTER DATA sheet then on my "OUT" sheet select an item from the drop down menu and have it immediately deduct the "Out" Qty from my "SoH" on my "Stock On Hand" sheet.

This must be done in such a way that if on line 3 I book out 5 units it takes 1000 - 5 and updates the SoH to 995. Tricky might be that if I then on line 60 book out another 10 the formula must deduct the 10 from the 995 and update the SoH to 985.

Option 2 would be the preferred method/target.

Can anyone assist?

Thank you

Kind Regards

Steve

[#2]

Assistance would be impossible without your sample file.

[#3]

@TwifooThank you for your reply, I have included a sample.

[#4]

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

[#5]

@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.

---

[#6]

@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

[#7]

@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. 

[#8]

@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.

[#9]

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.