Duplicate entries across 2 worksheets

[KA_Analyst]

Hi

I have searched for the answer to this before I posted but can't find anything that helps.

I have 2 sheets of data.

Sheet1 has a reference in column D
Sheet2 has a reference in column D

I need on a third worksheet to show whether the reference in Sheet2 appears on Sheet1. If it does, I need the sheet2 one to be highlighted

I can't do a sort, I can't use macros and I can't use filters

Someone please help - been going round in circles for days

[NBVC]

To highlight items in Sheet2 that don't appear in Sheet1.....

1. Select the range of items in Sheet1 and name that range through Insert|Name|Define.... call it something like MyList

2. In Sheet2 select the range to highlight and go to Format|Conditional Formatting and select Formula Is from 1st drop down menu....

3. Enter formula: =And(A1<>"",Isna(Match(A1,MyList,0))) where A1 is the top cell you selected.

4. Click Format and choose colour from Pattern tab...

5. Click Ok and click Ok again to Finish.

[KA_Analyst]

Can I do that in the 3rd worksheet though and have the formatting in the 2nd? Can't touch the two original spreadsheets with formula or any extra columsn as it's imported into a database

[NBVC]

You mean you can't name the range in Sheet1? That's the only thing I suggested... there are no formulas or columns added in either sheet....

You can copy the 1st sheet to the 3rd and name the range in that sheet instead...same result.

[KA_Analyst]

Thanks so much for your help. Sorry I misunderstood what you were saying. Read your post again, tried it and it works perfectly without maing any difference to the import into the database. I'm happy, customer is happy, everyone's happy Thanks again