Auto-Generate Due Date based on Ship Date, and other things

Hello!

I have been reading through this forum for quite some time now and decided it was easiest to get my answers by describing my own specific spreadsheet. I am a very basic user and want to accomplish some rather complex things.

My company designs and manufactures components for the biopharmaceutical industry. Essentially, it is just a fancy job shop. I am in the process of creating a master production matrix that will keep track of all aspects of each job, including static details such as job #, part #, customer info, etc. I also want it to generate a couple fields automatically based on the data that is input into other related columns. Here are the spots that are giving me the biggest problems:

- The column "Award Date" is directly related to the column "Due Date", in that we always set the due date 30 days out from the date on which we receive a PO. I want the "Due Date" auto-fill when I enter an "Award Date". There is no need to make this holiday/weekend sensitive.

- The column "Ship Date" is directly related to the columns "Terms", "Follow-up" and "Payment Due Date". I will be filling in "Ship Date" manually on the day that we send a job out. Based on the payment schedule specified in the "Terms" field, I want the "Payment Due Date" to auto-fill itself in. For example, if we ship a job on 01/01/11 and the "Terms" for said customer are Net 30, I want to see the "Payment Due Date" field auto-fill with 01/31/11.
"Follow-up" is related to these columns in that I want it to prompt our accounts receivable department to follow up with the customer 10 days before the Payment Due Date. This is to ensure that all documents are in order and that there is nothing else needed before they can cut us a check...lots of the larger customers will find any reason to avoid paying on time and I specifically DO NOT want to give them anymore excuses, hence the follow-up. To follow the same example as before, the "Follow-up" field should have auto-filled the date 01/21/11. I imagine that with a more complex formula such as this, one would need to ensure that fields were auto-filled in the correct order, so that the necessary information would be in place for the next piece of the formula to function properly. Thus, "Follow-up" must be related directly to the "Payment Due Date", which is directly related to "Ship Date" plus "Terms". I can picture the workflow in my head but alas I cannot put it down on a spreadsheet.

- There are a series of columns specifying the dates on which specific job processes should be completed. These will all be set manually by the production manager to give the machinists an expectation of when they should finish each step. They will replace the date with an X once each step has been completed, demonstrating that it has been done. This is to help us keep tabs on exactly where each job stands.
I think it would be awfully great if the due date fields that are overdue or still open once the date is reached would turn red. I think it would be even better if, say, 3 days before the due date the field would turn yellow. This would be an excellent visual cue for us to glance at quickly and see that something was close to the deadline or not completed when it should have been. So in other words, the field is manually filled with a due date for that process and if that date is reached WHEN THE FIELD STILL CONTAINS A DATE AND NOT AN X then it will turn red. Similarly, it would turn yellow 3 days before it turned red, but only if the field still contained a date and not an X. Can we do this? I don't need the fields to change back to their original color because it would be good information for me to know how often each process was late or close to it.

Believe it or not, these are the ONLY things I need help with, lol. I was able to work out hyperlinks to each job's related schematics and company information, which was awesome all by itself. Getting this other stuff in place would make production soooo much easier!! I have attached my sheet as it is with a couple rows filled in manually to help describe what you would see. I filled in the second row to illustrate the desired outcome of the formulas that I am hoping someone can help me create.

Thanks for all the help and please do not hesitate to email me directly. cjmorin@overlookindustries.com

Chris