Excel query re formatting cells

shopndrop

I have a work related problem where I need various staff to enter data into a spreadsheet and even though I format the cells e.g. date format, colleagues change the format so I end up with a mix.

Is it possible to lock the format in a range of cells or in individual cells in Excel so that staff can only enter the date in the specified format.

Whitefiver

Hi,

I have Excel 2003 and Tools/Protection/Protect sheet does have a Format Cells option. However, I have not tested it, and from what I recall protection is not necessarily intuitive - you will have to play with the "locked" flag on the protection tab of the Format Cells dialog too.

Sorry, I cannot really help more, but experiment, and I'm sure you will work it out.

Regards,

White.

Daft_Pegasus

Could you have another locked column which looks at the date they enter and formats it the way you need it?

johnmc

If you highlight the range and then right click. Select the Protection tab and UNCHECK the lock.

Now goto Tools, Protection and protect the sheet. In some versions you will be able to specify what the users can do.

Not fool proof, though, as anyone with a bit of knowledge can easily remove the protection. But at least they've got to think about it!

wakandem

You could use a cell for each part of the date.

totalsolutions

I picked up this link from a member here.. Pleased to pass it on
http://www.mrexcel.com/forum/index.php

shopndrop

Thanks to all above suggestions. Will try working my way through them to see if I can achieve what I need.

shopndrop

Have decided to go with data validation as at least doing it this way will stop colleagues entering data in a different way to what they have been asked to.