Formula needed for planning chart - hard to explain in title !

[chrisnewton]

Hi all

I am trying to make a production planning spreadhseet with excel and i am almost there. Its a bit hard to explain but ill try my best! On the attached spreadsheet you can see on the left hand side 6 machines that we have. 325 D,A,B,C,E and E25LT1. The Job number, part number qty etc are jst normal, nothing fancy. When you put something in the hours box it automatcially starts filling the week in on that particular machine. All that works great!

On the bottom of the spread sheet you will notice some figures, 0.00,1.00,8.00,21.00 and 34.00. these are the man hours that are left for each remaining day that automaticall deducts as you plan hours in to machines.

Here is my problem..as you can see in this test data i have no man hours remaining on monday and only 1 remaining for tueaday. If i wanted to plan another job no on the E25LT1 (say h hours) i would like it to know this and automatically plan it in the next slot (on the wednesday). Is there a formula to do this? or is it just a bit to complicated for Excel?


thanks for any help

chris

[Ursul]

Ok so I'm sure there is a wonderful formula somewhere for this but have you thought about using Conditional Formatting?

[chrisnewton]

hmmm no i havent...not sure how i could use that in this case??

[Ursul]

OK so further to my suggestion I have just done the columns for Monday. That is if the result in L49 = 0 then all the column turns to a light red as a warning that you have no more hours available for that day. You could also expand it so that if it were less than 8 for example you could have another colour as a warning. Just a suggestion, but see attached.


Newplan.xls

[chrisnewton]

Thanks for your help on this, unforutnatly what you have done does not appear to work on mine? it looks the same as what i had already done?

Thinking about conditional formatting though im not sure it will work as i want it to physically plan the job into the wednesday, ie it knows that monday and tuesday is full and starts the job on Wednesday.

again thanks for your help on this its much appreciated