Comparing a number against a list of numbers

[seadog101]

Hi,

I have a workbook that tracks pieces of equipment, part of the data is the serial number of each piece of equipment.

One of the sheets in the workbook is a compiled list of all the serial numbers of the equipment we think we have.

What I want is some way of highlighting to someone who is filling in data about these bits of equipment, that a serial number they have just input is not on the compiled list.

If I use the data validation 'list' option that stops people inputting a serial number that isn't on the compiled list. I still want them to be able to input the 'wrong' serial number, just have the cell highlighted, or a formula in an adjacent cell which then shows a 'correct' or 'incorrect' serial number message.

What's a neat and tidy way of doing this?

 

[nemmi69]

You could turn the list you have in a drop down then they can only pick those serial numbers

 

[Georgiboy]

How about conditional formatting, the list in column F would be on another sheet:

Book1
	A	B	C	D	E	F
1	Inventory list					Inventory we think we have
2	1					0
3	1					0
4	1					0
5	1					0
6	1					0
7	1					2
8	1					0
9	1					0
10	1					0
11	1					0
12	2
13	2
14	2
15	2
16	1
17	1
18	1
19	1
20	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A20	Expression	=COUNTIF($F:$F,A2)>0	text	NO

 

[seadog101]

How about conditional formatting, the list in column F would be on another sheet:

Book1
	A	B	C	D	E	F
1	Inventory list					Inventory we think we have
2	1					0
3	1					0
4	1					0
5	1					0
6	1					0
7	1					2
8	1					0
9	1					0
10	1					0
11	1					0
12	2
13	2
14	2
15	2
16	1
17	1
18	1
19	1
20	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A20	Expression	=COUNTIF($F:$F,A2)>0	text	NO

Thanks @Georgiboy, that's exactly what I would like to happen. I'll give it a go and report back.
Seadog.

 

[seadog101]

You could turn the list you have in a drop down then they can only pick those serial numbers

Unfortunately, they need to be able to input a 'wrong' number not on the list, as some equipment isn't on the compiled list, or they may simply typed in error.

Once they know the serial number is wrong they need to investigate why we have the item that isn't on our compiled list.

 

[Peter_SSs]

What I want is some way of highlighting to someone who is filling in data about these bits of equipment, that a serial number they have just input is not on the compiled list.

If I use the data validation 'list' option that stops people inputting a serial number that isn't on the compiled list. I still want them to be able to input the 'wrong' serial number

I think that you can do both (allow them other entries and warn them if not on the list) with Data Validation.

Here is my sheet with Data Validation (List) set up in D1 for serial numbers of 1, 2, 3 and 4

seadog101.xlsm
	B	C	D
1	SN
2	1
3	2
4	3
5	4
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
D1	List	=$B$2:$B$5

But in the Data Validation dialog go to the Error Alert tab and set it up like this.

Now I can enter a serial number not on the list and get this warning - with choices of how to proceed.

You may wish to also add Conditional Formatting as suggested by @Georgiboy so that the entry remains highlighted if the user chooses 'Yes' in the above dialog.