05-23-2019 05:40 AM
05-23-2019 05:40 AM
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.
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.
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?
05-28-2019 12:49 AM
@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?
I suggest that your opening inventory must be included in the In Sheet, Thereafter, On Hand will simply be In - Out.
05-28-2019 01:10 AM
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.
05-28-2019 02:40 AM
As I stated earlier, On Hand would simply be In - Out, with this formula:
The cumulative quantity on hand is determined with this formula:
Test the foregoing formulas in the attached version of your file and inform me of your thoughts thereon.
05-28-2019 10:25 PM
by Grimace on June 03, 2020
by Ingeborg Hawighorst on May 13, 2020
by cuong on April 22, 2020
by cuong on April 15, 2020
Posted in Microsoft Ignite The Tour 2019 on February 14, 2020