IF formula to highlight?

P

pugsly8422

I have a main worksheet with a list of everyhing, and then 12 more worksheets
with smaller lists. What I am doing is typing in something on one of the 12
worksheets and using vlookup to find that on the main worksheet and transfer
the numbers that correspond. Here is an example:

Main Worksheet
Bob 1 4 3 9 2 7
Shawn 2 3 4 7 5 8

Other Worksheets
I type in "Bob" and the corresponding numbers come up next to Bob's name.


What I would like to do is have the program automatically highlight the name
on the main worksheet whenever it is typed in on one of the other worksheets
to make it easier to see who has already been used.

I'm thinking I need to go to the main worksheet and put some type of "IF"
statement in with the names. I would probably use vlookup for that too, and
just have it search each of the 12 pages, if it finds it, highlight the name,
and if it doesn't find it, do nothing.

Any help is greatly apprecaited, let me know if there is anymore information
you may need.

Thanks in advance!
 
B

Bernard Liengme

It is unfortunate that 3D formulas (those referencing multiple sheets)
cannot be used with many function.

I have names Ann, Bob, Joan, ... in column A of Sheet1 (A1:A20)
In column A of other sheets, I may have one or more of these names

In E1 I have
=COUNTIF(Sheet2!A:A,A1)+COUNTIF(Sheet3!A:A,A1)+COUNTIF(Sheet4!A:A,A1)
You will need to expand this to cover 12 sheets
This is copied down column E to E20 .
The resulting numbers tells me how many sheets have each name.

Then I selected A1:A20 and used Format | Conditional Formatting
I set this up to as:
Formula is =E1>0 and used a Pattern to make the cells green

So I have "highlighted" all names that appear in other sheets.
I could use two conditional formats
=E1>1 to be red (when the name appears more than once)
and
=E1>0 to be green (when name appears only once)

Any help? best wishes
 
B

Bernard Liengme

I forgot to explain: you need the count in column E because conditional
formatting does not permit references to other sheets. Pity!
 
R

RagDyer

Just mentioned it as a point of information Bernard.

Data Validation and CF both automatically give that *false* error message
when trying to reference another sheet using the normal syntax.
 
Top