List - conditional formatting help please

[RickeyS]

Ok need some help, here's what I want to do, I want to make teams of people and not put a person on more than one team (these teams will be on sheet 1). My list of names to chose from will be on sheet 2 and sheet 3 (I don't want to combine sheet 2 & 3).

1. How would I show on sheet one if I used a person in two places (conditional formatting I assume)

2. On sheet 2 & 3 how would I show that a person was already put on a team on sheet 1 (again I assume conditional formatting)

I know where to place the formula, I just don't know how the formula would be written.


Thanks for your help,
Rick

 

[RickeyS]

So I did figure out how to show on sheet one if I used a person twice, now I just need help on number 2 above, how to show on sheet 2 & 3 if I used a person on sheet one.

Thanks,

 

[Eric W]

Let's say your list of names is in column A on sheet1, and column B on sheet2.

On sheet2, select column B. Click Conditional Formatting > New Rule > Use a formula and enter:

=MATCH(B1,Sheet1!$A$1:$A$100,0)

choose a fill color and click OK. Repeat for sheet3.