Formula Help

Question

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.&nbsp;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.&nbsp;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.&nbsp;Option 2 would be the preferred method/target.&nbsp;Can anyone assist?&nbsp;Thank you&nbsp;Kind RegardsSteve

twifoo · Answer

Assistance would be impossible without your sample file.

steve brand · Answer

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

twifoo · Answer

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

steve brand · Answer

TwifooHi and thank you for your reply.&nbsp;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"?&nbsp;Any chance of sending an example?&nbsp;Thank you&nbsp;Kind RegardsSteveTwifoo&nbsp;wrote:I suggest that your opening inventory must be included in the In Sheet, Thereafter, On Hand will simply be In - Out.&nbsp;

steve brand · Answer

Twifoo&nbsp;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.&nbsp;RegardsSteve

Forum Discussion

Formula Help

8 Replies

Resources