Populating a number of cells dependent on the value of another cell

Greetings All,

This is my first thread here, so I hope I am doing everything right... Basically, I have a spreadsheet showing how long it takes to go through certain processes. Say, Part A takes 2 days in Process A, 3 days in process B, and one day in process C. I have another spreadsheet that tells what day these parts are supposed to enter each of the processes (8/7/10 Part A enters Process A, 8/9/10 Part A enters process B and so on...) as well as the quantity. My goal is to make a more visual representation of where each of these parts are in each process over the whole schedule. So I want a spreadsheet with processes on top and dates on the side. I need a script that can look at the date to see when the process starts, then look at the cell that has the time information (days) and then take the quantity of parts and place it in the spreadsheet on the appropriate day and then copy that part quantity below it a number of times =to the # days it takes to complete the process.

So, if part a takes 2 days in process A and there are 20 pieces to be made starting 8/7/10, I want it to put "20" in the column for process A and in the row of 8/7/10 and 8/8/10. I also want this to be able to look through the rest of the schedule and be able to add up any other parts that would so happen to need to be processed on that day. I know that was a mouthful, but I would appreciate any help. I have attached a excel file that should help visualize this. Thanks.

The due date is given and so are the process times. So I take the due date and by subtracting the process times, I get the location of the part for each day.

To add to this further... I have been using a SUMIF(range, criteria, sum criteria) function to look at a huge schedule that shows all of the dates of when each part is going to be in which process, then compare it to the nice neat destination schedule, and then add the number of parts and put in the appropriate cell. Now, I want this function to work in the same way but for this new effort. I want it to be able to concatenate the part numbers so in each cell there will be a list of all of the parts in that process on that day. I have updated the spreadsheet to give you an example.
Another thing that might make this simpler is that I have another schedule that just shows the parts due for that week. It shows the processes and the dates which each of those parts will hit each process (I've added this into the workbook). I have been scouring the internet for solutions and found someone that did something similar using the difference between dates here. I've been working on this for a few days without much luck and any help would be greatly appreciated.

Hey All,

I am still looking for a little help on this one. Even if someone can help me get started or point me in the right direction it would be much appreciated. Thanks.

I realize I might not have been so clear in describing what I am trying to do, so I made a bit more concise description as well as updated the excel file.

I am trying to create a scheduling tool for a manufacturing process that has several steps and several unique parts. I have attached an excel file that should help visualize all of this. What I am given are the dates the customer wants the parts, the quantity due, and how long it takes for each part to get through each process. What I do is take the due date and subtract each of the process times until I end up with a start date. For example, if my part is due today and I have three processes that take two (2) days each, I know I had to start the part six (6) days ago. Currently I have been able to make a schedule that shows the quantity of parts to hit each process on that day [SCHEDULE(HAVE)].

I need to make a VBA script that will first use a blank schedule that shows process vs. dates [SCHEDULE(WANT)]. I then want it to take a date, compare it to the dates in [DUE 8.16.10], and then concatenate all of the part numbers that are due in that process on that. I want it to be able to do this for all of the dates and processes to fill up the schedule. I have done this before with a SUMIF(range, criteria, sum criteria) function except for the concatenate part (it instead summed the quantities). What would be a dream come true is if it could not only concatenate the part numbers but also attach the quantity from [QUANTITY DUE] (e.g. 121[3], PART#[QUANTITYDUE]).

What I am having particular difficulty with, is that I want to make a visual way of showing where the part is in the manufacturing line each day – not just showing the day it starts each process. So, if I know a part takes 2 days in process A, I want to have a script that copies it 2 times – once on the day it starts that process and once on the next day that it is still in that process. The process times are highly variable, so lets call it n times.

Please feel free to ask me any questions that can help clear up any confusion. I thank you very much in advance for your time and effort.