Conditional Formatting And Countif

[cafe]

I am trying to use in conditional formatting the following formula:

=IF(COUNTIF(InputPC,F10)>1,TRUE,FALSE) So it will highlight any duplicates.
But the way i setup the spreadsheet with "Select" as the default is no working. Any suggestions? or am I doing something wrong. Please it is urgent!!

The spreadsheet:

in row 10 columns F thru AT, I have a data validation with a list. The list has "Select "as a default. In column E12 thru E50 I have accounts:


1001 1002 1003 Select Select ..... etc

Marketing
Admin
'
'
'
etc.

Thank you
Cafe

[Bob Phillips]

Cafe,

Firstly you only need a formula of

=COUNTIF(InputPC,F10)>1

as that returns TRUE or FALSE as required by CF.

Then, what is InputPC defined as?

When you setup CF, select F10:AT10 and add the condition formul as above.

--
HTH

Bob Phillips

"cafe" <cafe.1tj5qb_1123646706.2457@excelforum-nospam.com> wrote in message
news:cafe.1tj5qb_1123646706.2457@excelforum-nospam.com...
>
> I am trying to use in conditional formatting the following formula:
>
> =IF(COUNTIF(InputPC,F10)>1,TRUE,FALSE) So it will highlight any
> duplicates.
> But the way i setup the spreadsheet with "Select" as the default is no
> working. Any suggestions? or am I doing something wrong. Please it
> is urgent!!
>
> The spreadsheet:
>
> in row 10 columns F thru AT, I have a data validation with a list.
> The list has "Select "as a default. In column E12 thru E50 I have
> accounts:
>
>
> 1001 1002 1003 Select Select
> .... etc
>
> Marketing
> Admin
> '
> '
> '
> etc.
>
> Thank you
> Cafe
>
>
> --
> cafe
> ------------------------------------------------------------------------
> cafe's Profile:
http://www.excelforum.com/member.php...o&userid=16145
> View this thread: http://www.excelforum.com/showthread...hreadid=394458
>

[cafe]

Thanks Bob,

The INPUTPC is the range F10:AT10.

Yes, that works fine except that I have "Select" through out the range. Is there any way I can have countif check for duplicates but ignore if the cell has the word Select?

See attached image

Please help, i need to resolve this today!!.

Reagards,

Cafe

[cafe]

Bob,

I solved my problem: I was just looking to hard I guessed. Here is my final formula

=IF(F10="Select","Select",COUNTIF(INPUTPC,F10)>1)

Thanks.

Cafe

[Bob Phillips]

Try this formula

=AND(COUNTIF(InputPC,F10)>1,F10<>"Select")

--
HTH

Bob Phillips

"cafe" <cafe.1tjxiw_1123682730.09@excelforum-nospam.com> wrote in message
news:cafe.1tjxiw_1123682730.09@excelforum-nospam.com...
>
> Thanks Bob,
>
> The INPUTPC is the range F10:AT10.
>
> Yes, that works fine except that I have "Select" through out the
> range. Is there any way I can have countif check for duplicates but
> ignore if the cell has the word Select?
>
> See attached image
>
> Please help, i need to resolve this today!!.
>
> Reagards,
>
> Cafe
>
>
> +-------------------------------------------------------------------+
> |Filename: DupCoutIf.gif |
> |Download: http://www.excelforum.com/attachment.php?postid=3682 |
> +-------------------------------------------------------------------+
>
> --
> cafe
> ------------------------------------------------------------------------
> cafe's Profile:
http://www.excelforum.com/member.php...o&userid=16145
> View this thread: http://www.excelforum.com/showthread...hreadid=394458
>

[Bob Phillips]

See my alternative.

--
HTH

Bob Phillips

"cafe" <cafe.1tk0az_1123686464.4369@excelforum-nospam.com> wrote in message
news:cafe.1tk0az_1123686464.4369@excelforum-nospam.com...
>
> Bob,
>
> I solved my problem: I was just looking to hard I guessed. Here is my
> final formula
>
> =IF(F10="Select","Select",COUNTIF(INPUTPC,F10)>1)
>
> Thanks.
>
> Cafe
>
>
> --
> cafe
> ------------------------------------------------------------------------
> cafe's Profile:
http://www.excelforum.com/member.php...o&userid=16145
> View this thread: http://www.excelforum.com/showthread...hreadid=394458
>

[cafe]

Hi Bob,

Fantastic! I used your alternative. It seems cleaner. Thanks for all your help.

Cafe