Solved Highlight duplicate entries multiple sheets

May 16, 2011 at 03:15:01
Specs: Windows XP
Hy all!

I am trying to highlight duplicate entries from different worksheets using conditional formating and is not woking. I've found this formula =NOT(ISERROR(VLOOKUP(A1;bannedworkers;1;0))) but it not works ; i'm using excell 2003.
I have two worksheets;in the first sheet on the first column (A) named bannedworkers I have a list of banned workers and in the second sheet on the first column (A) I have a list with workers. I want that when I type a banned workers name in the second sheet, first column, the cell to highlight in the color chosen in the conditional formatting.
I've used this formula in conditional formating but it not works =NOT(ISERROR(VLOOKUP(A1;myColumn;1;0))).

What is the problem???


See More: Highlight duplicate entries multiple sheets

Report •


#1
May 16, 2011 at 12:13:47
✔ Best Answer
You should be able to accomplish what you want, but you will need to use a Named Range

in the first sheet on the first column (A) named bannedworkers

Select your range of cells, IE A1 thru A50
On the Task Bar/Ribbon
Select Formulas
Select Define Name
In the Name Box, give your range of cells an appropriate name like: NameList
Click OK

in the second sheet on the first column (A) I have a list with workers. I want that when I type a banned workers name in the second sheet, first column, the cell to highlight in the color chosen in the conditional formatting.

On your second sheet where you want the Condition Formatting to go:

Conditional Formatting 2007

1) Select your range of cells: IE A1 thru A50

2) On the ribbon click Conditional Formatting

3) Click on New Rules, it’s near the bottom of the dialog box.

4) Click Use Formula to determine which cells to format.

5) Enter the formula: =COUNTIF(NameList,A1)=1
EDIT: Shorter formula: =COUNTIF(NameList,A1)

6) Click on the Format button

7) Select the Fill Tab

8) Select a pretty color

9) Click OK

10) Click OK

See how that works for you.

Just be aware that the names have to be Exactly the same, and spaces are considered characters.

MIKE

http://www.skeptic.com/


Report •

#2
May 16, 2011 at 13:41:41
Thanks MIKE!
The trick was to define a name for the entries in the first sheet and booth yours and my formula works:
=COUNTIF(bannedworkers,A1)
=NOT(ISERROR(VLOOKUP(A1;bannedworkers;1;0)))

and another formula that works is

=A2=(VLOOKUP(A2,bannedworkers, 1, 0)

Anyway thanks a lot for your help!


Report •
Related Solutions


Ask Question