Hello, all,
I've been browsing Excel forums for years now, but I've never felt the need to actually join one until now. I usually like to find solutions to my problems by myself, sometimes using various pieces of information I find here and there. But I have come to a full stop, due to lack of enough VBA knowledge at the moment - and I'd really need some guru's help.
I've been challenged to create an Excel file that can provide an overview of the Materials Dept. Planning of the production vs. the Production Dept. actual realization dates. As you can see in the attached file - there are 2 sheets: the one labeled "Planned vs. Realized" is the one in which data is put in, as follows: the Mat. Dept. inputs the blue marked data, while the production should fill in the reddish one. The purple cells will be automatically calculated, based on the type of Finished Product on each specific order, and its respective production times.
First thing I need help with would be some code (I presume) that does the following: every time a new order line is put in by the Mat. Dept - a new row gets inserted underneath, copying the order date, the order no. and the FP Type, as well as inserting the formula for calculating the Prod. Std. Finish Date
Once all of this is done - we get to the really hard part, which is the Order Overview sheet. What I managed to do already (with google help...) is create a function that allows me to see all the orders planned to start at a certain date. Also, I have included that button to calculate the formulas, as for some reason it gives some errors without it. I don;t know if there's a better way to do that, but for now I am pleased with that.
Now, coming to the real challenge: what I need to accomplish is this: as stated before, based on the FP Type, each order has a specific time frame in which it is finished. Let's say that for order "abcd" in the attached file - this frame is 13 days. In the overview - I would need to see order no. "abcd" listed under each and every date from the Prod. Real. Starting date (02.01.2012) to the Prod. Std. Finish Date (02.01.2012 + 13 = 15.01.2012). And that should happen with all orders, of course, so in the end I can see for a set period of time what's my planned production coverage.
I would appreciate any help from you guys, and i hope I have been clear enough explaining what i want...
Thanks a lot in advance,
Radu
Cross-posting: http://www.mrexcel.com/forum/showthread.php?t=600187
http://www.ozgrid.com/forum/showthread.php?t=160897
Bookmarks