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