Excel 2007 : Data Validation Question

**jasonmcbride** · 10-29-2008, 06:03 AM

I have used Excel 2007's Data Validation function to tell it what it will allow in a cell, but I want it to disallow some numbers, is this possible?
I don't want users to be able to input 1, 2, 3, 5, 10 and 20 into cell A1.

Please help!

**royUK** · 10-29-2008, 06:27 AM

Usually in Data validation you can set a max and min for a range of numbers. I haven't got 2007 today, but I can't recall it being part of the changes.

**jasonmcbride** · 10-29-2008, 06:32 AM

Yeah it does but 1,2,3,5,10 and 20 are the only ones I want it to exclude. I want to be able to put 4 or 6.5 for example and if I exclude between 1 and 20, it won't allow me.

**Andy Pope** · 10-29-2008, 06:47 AM

Data validation > List > 1,2,3,5,10,20

You can hide the dropdown arrow if you want.
Make sure the Error warning is ticked.

**jasonmcbride** · 10-29-2008, 08:13 AM

yeah but I DON'T want it to allow those numbers. Anything but 1,2,3,5,10 and 20

**Andy Pope** · 10-29-2008, 08:33 AM

My bad I mis read your post.

Create a Name, BAD_NUMBERS, with this reference.

={1,2,3,5,10,20}

Then for the data validation use Custom with the following formula. Where $D$5 is the input cell.

=ISNA(HLOOKUP($D$5,BAD_NUMBERS,1, FALSE))

uncheck Ignore blanks and add Error message.