Data Validation - 5 Digit Number with exclusion list

[tomexcel1]

Hi All,

I'm looking for a data validation formula that can achieve the following:

• Only allow the input of a 5 digit number
• Don't allow the input of the 5 digit numbers in a list (The list is in cells F5020:F5045)

Any help is much appreciated

Thanks
Tom

 

[tyija1995]

Hi Tom,

In cell A1 (assuming A1 is where the custom rule is applied) try this:

=AND(ISNUMBER(A1),LEN(A1)=5,COUNTIF(F5020:F5045,A1)=0)

This says A1 must be a number, it must have length of 5 and it must not be equal to any of the values in F5020:F5045

 

[tomexcel1]

Thanks Tyija

When I use this formula in data validation it seams to let me enter anything in the cell. Any ideas why?

Thanks
Tom

 

[tyija1995]

What range do you want to apply the set of rules to? I.e. numeric, 5digits, not in the list mentioned?

As for why you can enter anything in the cell - how have you referenced the data validation rule exactly?