Data Validation and Current Date ?

[Peter]

I am new to data validation in Excel and would like to seek your advice on
the following items

1) For a particular column, I would like end users to enter 6 numeric digit.
I have tried to choose Decimal with min 6 & max 6 but doesn't work. Then I
use "Whole Number with min 6 & max 6" but also doesn't work. What
validation criteria should I use ?

2) End users don't like to enter "Today's Date" by themselves. I try to use
the formula "=Today()". However, I believe the date in that cell will
change every we open the spreadsheet. Is there any easy way to show the
date they last modify the spreadsheet ?

3) They would like to keep the column width to 30 and I would like to know
how to protect the width ?

Thanks

 

[Dave Peterson]

1. Use whole number and between 0 and 999999 (or 100000 and 999999, depending
on what you want).

2. Train them to use ctrl-; (control - semicolon). It's the shortcut key for
the date. (ctrl-: will show the time.)

If you want to keep track of changes on a row-by-row basis, take a look at JE
McGimpsey's site:
http://www.mcgimpsey.com/excel/timestamp.html

3. You can protect the worksheet
tools|protection|protect sheet in xl2003 menus.

But lots of things can't be done if you protect the sheet. You'll want to test
to see if this is worth it.

 

[Peter]

Dear Dave,

For point 2, they would like to show leading zeros. For instance, if they
enter 123, they would like to show 000123, is there any way to do so ?

Thanks
Peter

 

[Dave Peterson]

Point #1???

Select the cell
format|cells|number tab|custom category
type:
000000

(xl2003 menus)

They can still enter 123, but when they enter the value, the formatting will
show it nicely.

(They can type the leading zeros if they really want.)

 

[Peter]

Dear Dave,

Many thanks for your help.

Thanks
Peter