Help with Conditional Format Formula

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.


"David K." wrote in message
...
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.

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


"David K." wrote:

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 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 InsertNameDefine
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 FormatConditional 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

--
Biff
Microsoft Excel MVP


"David K." wrote in message
...
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.

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." wrote in message
...
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.


"David K." wrote in message
...
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.

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

"T. Valko" wrote in message
...
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 InsertNameDefine
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 FormatConditional 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

--
Biff
Microsoft Excel MVP


"David K." wrote in message
...
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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"David K." wrote in message
...
Thanks Biff. I think I've run into that little 'conditional format
referncing' issue before. I'll apply this concept.
Thanks again.

"T. Valko" wrote in message
...
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 InsertNameDefine
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 FormatConditional 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

--
Biff
Microsoft Excel MVP


"David K." wrote in message
...
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.