Stock control - Available stock based before and after issuing, returns and purchases

[WesvdB]

Hi there everyone.

Hope this mail finds you well.

I please need your help with what to me is a real head scratcher. I'm pretty sure it can be done and would appreciate any input.

Thank you in advance.

I have attached the document with the same notes and an example filled in on the sheet.

This basic stock control system is based on 4 sheets.

On sheet one (Stock Items), the person in charge will be able to filter stock items based on either the item name, make or description to get to the item number. Once the item number is obtained it can be used on sheet 2 (Booking Out Form) and Sheet 3 (Purchasing Form)

On Sheet Two ( Booking Out Form), if the stock item number is entered into Column A, the formulas in Columns B - D automatically reflect the associated values related to that item number on Sheet 1 (Stock Items). The same is true for Sheet Three (Purchasing Form)

Sheet Four (Stock Control) has the following relevant columns: Column F (2021 Opening Stock, Column G (Stock Booked Out), Column H Stock Returned, Column I (Total Stock Ordered), Column J (Total ordered Stock Returned) & Column K ( Available Stock)

On Sheet 2 (Booking Out Form), I have two columns for availability. The first, Column E, has to auto populate the current availability, before stock is booked out, based on the item number and should get its information from Sheet Four (Stock Control). I then have a two columns for the date and time (Columns F & G) respectively as well as columns for the quantity taken and possible returns of unused stock (Columns I & J respectively) after which there is another Availability column, (Column K)

It might sometimes happen that on the same day, two different people requests the same stock item or one person does so twice or more on Sheet 2 (Booking Out Form). If the person responsible for stock management adds the item number in Column A, I would like column E to reflect the current availability of that Stock Item in Column E.

Then, the responsible person captures the date, time, person signing out and quantity taken on Sheet 2 (Booking Our Form), which should calculate the availability after stock is booked out in Column K. If at the end of the day some of the stock taken was not utilised for that day, the person taking it can book it back into the store and the stock controller can capture the quantity of stock returned in Column J, which should then again update Column K.

This would be relatively simple if I wanted to just keep tabs on running stock, however I would like the availability in Column (E) to be based on the date and time before the stock was booked out. Then once the stock is booked out the availability in Column K is updated with the new stock level. The next time somebody comes to book out that same stock item, the availability in Column E provides the available stock on that date and time.

Another factor as per the example is transactions on Sheet 3 (Purchasing Form). In the example I have Johan booking out Stock Item 1 on 19 Jan 21 at 9:43 am, then Wes booking out that stock item on 19 Jan 21 at 11:38 am on Sheet 2 (Booking out Form). Then on Sheet 3 (Purchasing Form), Item 1 is purchased on 19 Jan 21 at 11:41 am. All of these transactions should ultimately update to Sheet 4 (Stock Control), I think, so when on 20 January 21 at 11:42 am Johan books out that same item again, the availability in Column E is the actual availability of the stock item in the store based on what was booked out, returned and purchased.

[AliGW]

Administrative Note:

I see no material difference between this and your earlier thread, to which you have not yet responded (help has been offered there) and you are allowed only ONE thread per issue here.

Please see Forum Rule #5 about thread duplication.

I am closing this thread, but you may continue here in the original thread: https://www.excelforum.com/excel-for...ock-items.html