How do I ensure only integers and with no duplicated values can appear in a column in Excel 2007?

Question

How do I ensure

1. only integers
2. with no duplicated values

can appear in a column in Excel 2007?

Answer 1

You check for duplicated values using something similar to the answers received in your question yesterday, then also check that the value is an integer.

Using A as the column, select A2 through as far in column A as you care about, then go to the Data Validation dialog. Choose custom and input the following formula:

=IF(ISNA(VLOOKUP(A2,A$1:A1,1,FALSE)),IF(INT(A2)=A2,TRUE,FALSE),FALSE)

As someone mentioned in your other question, this will only work for input values, not dragged ones.

NOTE: The standard column choosing method of A:A doesn't appear to work in this scenario because apparently the code always sees the cell itself as a duplicate (of itself), so it always fails.