How to input only unique values in a column in Excel 2007

Question

I'd like to know the easiest way to make sure that only unique strings are entered in a particular column in MS Excel 2007? I know I have to put a formula in the Data > Data Validation, but I don't know how.

Say, I'm entering data anyplace within column B, and I want to know if the inputting value is already elsewhere in the column (above or below the current cell) and don't allow if there is a duplicate.

Edit:

1. I've seen answers like this one on this site, but they all have an range say from B1 to B20, how do I have this validation on the entire column, is this possible?

2. Seem like when I drag on the cell, duplicated values can appear, it is only when I try to input the value that this validation works. So, how do I restrict on the dragging as well as manual input on this?

Answer 1

Instructions for avoiding duplicates for Excel 2000 are here.

Answer 2

Also, just a side note.... when referring to particular cells in a column (like in your example) you would refer to them as B1:B20. To refer to the whole column, you can use B:B. That describes an array of all data in the B column. Hope that helps.

Answer 3

You can only deal with copying over validation with VBA:

http://spreadsheetpage.com/index.php/tip/ensuring_that_data_validation_is_not_deleted/

Answer 4

I answer only your question 2. You have to write your own Private Sub Worksheet_Change routine.