Count and conditionally format values not in other list on other sheet

[Knawl]

Hiya, can anyone help?

In sheet1 I have a range called table1 in which one column contains a list of reference numbers. In sheet2 I have another list of reference numbers.

I want to determine if the column in sheet2 contains any values that do not exist in sheet1 and if so highlight them. The list in sheet2 does contain duplicates.

On sheet3 I would like a formula to ideally list those missing reference numbers or at least count them so the user can be made aware.

[canapone]

Hi,

in sheet2 list in A2:A1000

You could use as format rule in A2

Please Login or Register  to view this content.

Hope it helps

[Knawl]

Hi I've tried that. It tells me I cant use references to other worksheets for conditional formating criteria.

[canapone]

Hi,

in sheet3 to get the list of missing items:

In A1 to be confirmed with control+shift+enter and them copied down

Please Login or Register  to view this content.

Hope it helps

[Punnam]

hi,

find the example sheet

to modify the range use name manager

Please Login or Register  to view this content.

Punnam

[canapone]

Hi,

here attached the file where I've some test: I'm using Excel 2010, probably Excel 2007 does not accept references to othersheets.

Array Formulae in sheet3 create a missing strings list.



Cheers

[Knawl]

Thanks,

Got the CF working with =IF(F2="","",NOT(ISNUMBER(MATCH(F2,data,0))))

Now trying the other formula but it keeps sticking at processing 0%. My sheet1 is 10000 rows so don't know if it's too much for it to cope with.