Formula in Conditional Formatting

[raystownlaura]

I'm trying to highlight dupe rows in Excel '03. In column Q I have concatenated all of the cells I want to check. For example, an entry in cell Q1 might look like:

John Doe 300 Whit Drive Pittsburgh PA 00001-5235

To highlight dupe rows of data, I am using condition formatting with the the following formula/conditions to highlight the row yellow, but it's not working:

=(IF(ISBLANK(A2),0,COUNTIF(Q$1:Q$500, $Q1)))>1

However, when I place a similar formula into cells... =IF(ISBLANK(A2),0,COUNTIF(Q$1:Q$500, $Q1)) ..., the values return properly as either 0 or X (number).

Can anyone see where my problem might be and why my rows will not highlight when I place the same formula in conditional formatting? I have a feeling it has to do with choosing absolute values???

Any help would be appreciated!

 

[SOS]

In the expression get rid of the = sign in the conditional formatting.

 

[Brianwarnock]

Its not the = but the >1 is outside the brackets
=(IF(ISBLANK(A2),0,COUNTIF(Q$1:Q$500, $Q1)))>1
shouldb
=(IF(ISBLANK(A2),0,COUNTIF(Q$1:Q$500, $Q1))>1)

Brian

 

[SOS]

That may be true Brian, but in the Conditional formatting it is ASSUMED by it that you are using a condition. You do not include the = sign for it.

 

[Brianwarnock]

If you don't put it in the system does, that is where the assumption comes in, but not when using formula is you need it or it converts your formula to ="yourformula"

Brian

 

[SOS]

If you don't put it in the system does, that is where the assumption comes in, but not when using formula is you need it or it converts your formula to ="yourformula"

Brian

I'm talking about the Conditional Formatting - you know that part? You choose Expression IS and then you put the expression. You don't put the = sign in front of it and that is what the OP was using - Conditional Formatting, not a formula in a cell (when then you do need the = sign of course).

 

[SOS]

And of course you can use the = IN the expression, it just should not be the beginning symbol when using the feature - Conditional Formatting.

 

[Brianwarnock]

Please don't insult me I can read, I can also test and have just done so it maybe different in later releases but in 2002 you need the =.

Brian

 

[SOS]

Please don't insult me I can read, I can also test and have just done so it maybe different in later releases but in 2002 you need the =.

Brian

Are you including that when using EXPRESSION IS as well as VALUE IS?

I've always seen errors when using the = at the beginning with Expression Is.

 

[SOS]

Oh well, count me as a pickle with no brain. I'm sitting here thinking of Access Conditional Formatting and this was Excel.

I shall now go dunk my head in a hot vat of oil and paint myself pink.

 

[Brianwarnock]

Oh well, count me as a pickle with no brain. I'm sitting here thinking of Access Conditional Formatting and this was Excel.

I shall now go dunk my head in a hot vat of oil and paint myself pink.

LOL, we all make mistakes, the good guys admit it.

Brian