Standardizing data

Hi Paul

Thanks for your questions. You can add validation rules to a range of cells by highlighting them then going to Data > Validation and setting the Allow value to show List.

You can then use the Source cell in the dialogue box to either list the items to appear in the dropdown, separating them with commas. Alternatively you could enter the = character followed by the name of a range previously created that contains the items to appear in the list.

If you need to modify the contents of list either click on a cell containing the validation you wish to modify, go back to Data > Validation and check the box marked "Apply these changes to all other cells with the same settings" OR modify the contents of your range. Either way your revised items will appear in the list to be selected.

With regard to the capitalisation formatting, Word has a text format that displays values as uppercase but Excel does not.

You might choose to use a function called Uppercase() that converts the text contents of a cell to uppercase.

Alternatively you may wish to create a macro. Unfortunately this is slightly outside the scope of the forum but I have found an example macro online that may give you a starting point.
http://www.ozgrid.com/forum/showthread.php?t=27087

I hope this helps - do let us know if you have any questions.

Kind regards,
Andrew