1

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?

3 Answers 3

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.

1
  • Thank you so much, this answered part of my question, upvoted!
    – Ray
    Jul 19, 2010 at 17:43
1

You can only deal with copying over validation with VBA:

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

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .