Hi Folks,

This is a big one and not sure if there is a solution, but would appreciate anyones input.

I have designed a sheet that will eventually give me the length of time it will take to complete a job. then i used a macro to transfer this info onto a sheet that can be printed out once all the jobs have been entered and calculated etc.

i would like to take it one step further if possible. The jobs need to be divided equally amongest 4 machines over a 12 hour shift period. Is there any way that i can do this using excel, i.e. when macro used the job will be allocated to machine A and but if job is for 4 hours and there is only a space of 3 hours it will be allocated to next machine.

i'll try my best to explain a little clearer what i have done.

My first sheet is my calculation sheet which has approx 7 columns in, each has a header which for each row i used data/validation/list and assigned a series to. So i have a number of drop down boxes in so i can now choose a product that needs building by choosing an item from each section. I used vlookup to attach numders to each lookup so i get the total operators required to complete the job. Once completed i have the parts per minute it should take to complete and how many operators.

Next i created a macro which saves this information into a compile sheet in the order that i want. At this point i also added two new boxes which remain blank so they can be filled in at a later stage. At the end of this row i then have 3 hidden cells with calculations in.
The first calc cell (N7) gives me my total minutes it will take to complete this job. The next calc cell (O7) contains an if statement and basically if N& is above/between so many minutes it will add break times to the total figure. The next calc cell (P7) then converts the result in O7 from minutes into hours and minutes.

Next i have a macro that copies this row from the compile shhet to a new sheet, insert a new row every time. Eventually i will then have a list of different type jobs each with two blank boxes with formulas attached to. The blank boxes titled order qty and total job in hours and minutes (the formula in this box is =P7).

The idea now is for me to fill in the order qty box with how many the job is for and then it will automatically fill in the total hours and minutes so i now kow how long it will take to carry out this job.

What i want now is to transfer this info (the total time in hours and minutes) to a gantt chart.
The jobs (hours/minutes) need to be divided equally amongest 4 machines over a 12 hour shift period i.e. job will be allocated to machine A and but if job is for 4 hours and there is only a space of 3 hours it will automatically be allocated to next machine B.

Can i get this info into some kind of gantt chart or something similar that will do the job? would appreciate anyones help with this.

thanks

charllie