|
Conditional Formatting And Countif
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,
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" wrote in message ... 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 |
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, 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 |
Try this formula
=AND(COUNTIF(InputPC,F10)1,F10<"Select") -- HTH Bob Phillips "cafe" wrote in message ... 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 |
See my alternative.
-- HTH Bob Phillips "cafe" wrote in message ... 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 |
Hi Bob, Fantastic! I used your alternative. It seems cleaner. Thanks for all your help. Cafe -- cafe ------------------------------------------------------------------------ cafe's Profile: http://www.excelforum.com/member.php...o&userid=16145 View this thread: http://www.excelforum.com/showthread...hreadid=394458 |
| All times are GMT +1. The time now is 06:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com