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

wow thanks, that's a great start! To be honest, the last of three points is more gratuitous than anything else, as I can easily tell if something is going to be late or overdue. I will most likely resort to manually changing the color so as to keep track of when things get to this point, for future reasons. Regardless, thanks again for what you have provided to me!

Chris

how do i make this formula work for every row in that column? what would i replace 2 with to get it to function properly?

i suppose i will just manually enter then in every field that i need them. i wont ever be getting over 20 or 30 active jobs at once anyway. i would still like to know how this is possible, though.

no worries, i just found the method!! very cool.

FYI, anybody who may read this who is trying to enter sequential formulas that follow the same scheme but for each respective row, use this method:

put a character in the field in the last row you want to contain the formula. I simply dropped a "1" into I30.

go back up to the top of that same column and enter the formula once, properly, for that specific field.

select the field with the formula and hit "Ctrl+Shift+Down Arrow" to highlight every single field in the column up to the one filled with "1"

hit "Ctrl+D" and bingo bango you have the appropriately formatted formula for each respective row!! slick!

Moving on to a bit more complex of a concept.

I want to know if it is possible to take the date generated in my follow-up field and have it automatically open up a notification in outlook.

I think this involves Visual Basic which is way over my head. Basically, i just want it to recognize that the date in one of the follow-up fields matches the current date, so when the spreadsheet is opened it will show notifications in outlook to let the person know that a follow-up is necessary on that day. i know...it is easy enough to scan the column and look at the dates, but as our Accounts Receivable woman is having more and more trouble seeing and reading things on screen, I want to give her all the help she can get. In other words, I want to make excuses based on bad vision a thing of the past!

I am curious, though....if this is possible, does one have to always bring up outlook first and then excel afterwards? or does the order really not matter?

thanks for your help so far!

does anybody have any help to offer in regards to the automatic color scheming of fields or the auto-popup of outlook notifications? real curious about how to accomplish these things.