Help with Conditional Format Formula

[David K.]

Hi,

How can I highlight cells in a list of numbers on Sheet1 where the number of
occurrences of that number on Sheet2 is not equal to 1?

Sheet1 has a list of unique numbers that will not change.
Sheet2 is an entry sheet were a list will be entered.
Assuming both lists will start in A1 and continue in column A.

On Sheet1 how can I highlight cells in the list where the number of
occurrences of that number on Sheet2 is not equal to 1?

In other words, using the list on Sheet1, I want to know if a number has not
been entered or entered more than once on Sheet2.

Hope this makes sense to someone. Thanks for any ideas.

 

[David K.]

There's probably a better way but this seems to work. On Sheet1: conditional
format formula
=SUMPRODUCT(--(A1='Sheet2'!A1:A3000),'Sheet2'!A1:A3000)<>A1

Don't tell my wife that I answered myself.

 

[Teethless mama]

=COUNTIF(INDIRECT("Sheet2"&"!A:A"),A1)<>1

 

[T. Valko]

There are a couple of extenuating circumstances that make this a bit more
complicated than it should be.

You can't *directly* refer to another sheet when applying conditional
formatting.

You can get around this by using defined names *but* you need a defined
formula that uses *relative references*.

Try this...

Rng refers to Sheet2!$A$1:$A$20

***This is important***

Select cell A1 on the sheet where you want the formatting to apply. Even if
cell A1 is not one of the cells you need formatted, we need to make this
named formula relative and you do that by making cell A1 the active cell
when you define the name.

So, Select cell A1 on the sheet where you want the formatting to apply.

Create this named formula
Goto Insert>Name>Define
Name: IsNotZero
Refers to: =COUNTIF(Rng,A1)<>0
**Make sure you use cell A1 as the criteria argument**
Ok out

Now, apply the conditional formatting...

Let's assume the range to format is A10:A20

Select the *entire* range A10:A20 starting from cell A10. Cell A10 will be
the
active cell. The active cell is the one cell in the selected range that is
not shaded. The formula will be relative to the active cell.

Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=IsNotZero
Click the Format button
Select the desired style(s)
OK out

 

[David K.]

The magic of microsoft seems to be preventing the exchange of replies
between the web inteface and Outlook Express again.
My thanks to Teethless mama for the response.
=COUNTIF(INDIRECT("Sheet2"&"!A:A"),A1)<>1
Very nice.

 

[David K.]

Thanks Biff. I think I've run into that little 'conditional format
referncing' issue before. I'll apply this concept.
Thanks again.

 

[T. Valko]

You're welcome. Thanks for the feedback!