I do this sort of thing all the time with my templates. I use a simple IF/THEN formula to leave a cell blank if a certain "check" cell is blank, but once it has data, it shows a default value. Of course, when I get to the cell, I can type over it with some other value, but until then the formula is there and waiting.
Let's assume Column A is your check column. Column B is the where you give the status of the project this particular row of data represents... and the default is TENTATIVE.
The formula you enter cell B2 is:
=IF(B1,"TENTATIVE","")
Now copy Cell B2 down the rest of the B column. Anytime someone starts data by entering a value in the first column, the word TENTATIVE appears in the second column.
Now that you see how to do that, adjust this to your project.
NEXT, in the menu DATA > VALIDATION you can define the only acceptable parameters for a cell. Select the entire B column, the bring up the Validation Options and set them to only allow the values for that column you deem appropriate. You can even customize the error message that will occur when someone enters the wrong thing.
For instance, my example above used TENTATIVE, you could validate and only allow TENTATIVE, CONFIRMED and CANCELED. This is done by making a list of those options somewhere (AA1:AA3) then putting that in the validation settings as LIST > Source: =AA1:AA3 and make sure the In-cell dropdown is checked.
|