validate list

[david brown]

i have a list of numbers, which should all be one of 12 possible "codes".
What is the easy way to check that the numbers are correct and highlight any that are incorrect entries.

I could use a large if function, =if(a1="123456", if(a1="7891011"........,"ok", "false")...... but this seams very clumbersome.
is there a way to use =IF( A1= any value of this array,"true","Fales"


any help apreciated.

 

[Atholl]

Try: Data -> Validation

Then in 'Validation Criteria', select Allow list. In the source box, either refer to some cells containing your numbers or type in the list, separated with commas.

HTH

 

[david brown]

thanks, but the problem is i have already got a list of several thousand numbers( codes ) from another source and need to validate this list to identify any incorrect values.

 

[NBVC]

You can put the list in a separate area and refer to it via a Match() formula


for example: =If(isnumber(match(A1,$X$1:$X$100,0)),"Match Found","No Matches") where X1:X100 contains the list to match up against

this could be copied down and also could be amended to use in a conditional format statement..... like so =Isnumber(Match(A1,$X$1:$X$100,0))

 

[Atholl]

Hmm, you could try using Advanced Filter?

 

[david brown]

bingo, many thanks.