Automating a Scheduling Template Using Many Cell References

Hi everyone,

I have a problem that I can't seem to straighten out on my own. I have a scheduling template that delegates items off of a given schedule to be inspected by one of two stations (employees). We inspect these items in the order that they are produced and allocate them to whichever station is free for that next item first. Attached is an example template of what we currently use. If you are interested in helping me with this problem, please take a minute to look it over, noting the cell references, look ups and other formulas that I currently use.



Now that you've given it a minute, hopefully you can see how I populate the items of a given schedule to each of the two stations based on the time that they finish the previous item. I start with allocating Item #1 to Station #1 and Item #2 to Station #2. Depending on who finishes their starting item first would then be allocated Item #3, and so forth until Item #20 has been assigned (by me simply typing in Item #3 in the next available row in their respective station schedules).

My problem arises when trying to automate this process, as it takes a few minutes to populate these columns myself and becomes burdensome with my daily responsibilities. I can't seem to avoid a circular reference somewhere in the process, whether it be when trying to assign the next item based on a lower finish time, the lower sum of the times in each station's schedule or the other ways I've tried to do this.


You'd be a great help if you can come up with a way to successfully do this for me. On the 2nd sheet of the attached .xls file I have shown how the example inspection schedule should be allocated among each station. Use this as a guide!


Additionally, as you have noticed by now, I also need to add in logic to account for break times that each station takes during production hours. In this example, they get a 10 minute break at 7:15 AM, 7:35 AM and 7:55 AM. I need the first value (and only the first value) in their schedules that reaches each of these break times to add 10 minutes to their finish time for the following item. This should be 3 additional pieces of logic within your formula; one for each of the break times. Currently, after assigning each item to the two stations, I add these breaks manually to the finish time field for the appropriate item.



I hate to ask for help like this, but I am out of ideas for how to accomplish this on my own and don't have hours to spend anymore debugging it on my own!

THANKS to everyone who takes a shot at it!

- Sole