[rmccafferty]

I am used to doing this in Access but don't see how to do it in Excel. And, of course, nothing is easy to find any more in the 2007 version of the program.

I want the a cell in a row to auto enter a value when people are doing simple data entry into a spreadsheet. I do not, of course, want that entry in every unused row ad infinitum in the spreadsheet. I suppose I could use a drop down box with only 1 option.

If I were in Access, upon entering a field, I could code it to enter the default amount I want. (Assuming that I didn't just set the default value when setting up the table and field. Don't have a clue how to do it in Excel. I have tried several options, but none have worked.

Thanks,
Robert

[JBeaucaire]

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.

[rmccafferty]

If I said DUH, you would understand? Thank you for the reply. Actually, in practice, I will nest the if statement you suggest inside another if that says if the test cell is null, to leave the destination cell null, then once the test cell has something the default entry will appear.

Again, Thank you.

[JBeaucaire]

You don't need to nest my formula in a null statement. If you use it as is it will do exactly the same thing.

=IF(a1
This means IF A1 has anything in it at all...

=IF(a1,"tentative","")
The following two parameters tell the formula what to do if it's true (")
The following two parameters tell the formula what to do if it's true (") and if it's false (null).

And as for the "duh" part, no worries. If you just open the validation tool I've pointed tool and read it a little, you will get it pretty quick. All of this is hard until you've used it twice, then the light bulbs go on.

Validation isn't 100% necessary, it just keeps people from putting unexpected values in cells where they NEED to follow a specific data format... like numbers only, or certain words only. It's something worth playing with since it eliminates user errors.