Program to generate Sales, Production and Stock data in one sheet

[Jayeshd]

Hello all,

I have created three different sheet - Daily Prod, Daily Sale and Stock. All sheets have one column "Item Code" as common.

I would need to identify against each "item code" what is daily production and quantity sold using data from three sheet where data is manually added.

Would any formula or VBA code will help here. I tried to search through internet but could find anything very useful

ANY help on this would be greatly appreciated. thanks

[Jayeshd]

Please let me know if you need any further help for above query?

[DaveDeV]

Hi J,

What would be a great help is if you attach a sample of the workbook and also some explanation as to whether or not the "item code" could occur more than once in the three worksheets.

The one problem I can see lies in the "Stock" as you need two values for this - Opening Balance and Closing Balance where:

Closing Balance = Opening Balance + Production - Sales

The reason for needing two values is that using just one will result in the value always showing either Opening Balance (if Production & Sales are not included) or Closing Balance (if Production & Sales are included).

So, please post a sample workbook as an attachment.

Dave

[Jayeshd]

Sure will post it asap, thankyou for response

[Jayeshd]

Hello Dave,

Find attached three excel - Daily Production, Sale and Stock Stock.xlsxDaily Production.xlsxDaily Sale.xlsx

Daily Prod contains item code machine wise produced daily, it could be same item produced daily,
Sale contains item code with quantity sold date wise
Stock contains item code and quantity as of some date

Need to create data from above three sheet which will help get item wise stock + prod - sale (on weekly or daily basis)

Hope this helps understand problem statement. Let me know if any other details is required. Again thanks for consideration.

[Jayeshd]

Hello ,

One way i identified is do pivot for all three excel and then in stock do vlookup from production and sale and generate data.

Is there better suggestion which would reduce manual work and help identify weekly and daily data. thanks

[ratcat]

G'day

What's attached is a suggest by no means an answer to your question.

[Jayeshd]

Hello,

thanks for your response, can you help me understand whether "date" field in sheet "Stock 2" is entered manually,

Also it seems we will have to create three column everyday? Correct.

Weekly data would be good enough to start with as will need to reduce manual work as much as possible apart from the data entry which needs to be done?

if any more suggestions on this would be really helpful. thanks

[ratcat]

G'day,

The supply of data was a bit over the place in regards of date range so I had to expand the range to a large one so you can see it work.

So its entirely up to you, if you like to manage the data daily, weekly, monthly or even quarterly.

To reduce manual work ? That's what I love about excel, I've made my excel workbook reduce hours of stress in my line of work.

The date field or value is now automatic after you input the starting week date in cell B3 on the 'Production' tab.

Have a play with by adding past information that you know is correct and see if the workbook matching your other records.