I am simply trying to add a conditional format that fills in a cell with a color if it is equal to any data that is in a list.
See attached.
I want Columns D & E to reference columns A & B to check if the name is there and to fill in the cells a color if it's true. I want to be able to add as many names as I want to A & B so I need a full column reference.
What is the formula I enter into the conditional formatter?
Thank you
Bump to top
I use excel 2003 to do a test. I think its ok in 2007.
I use a name called last to denote last row.
You can press Control + F3 to see it.
Conditional formula is as follows:
Attached file is a sample.
Please Login or Register to view this content.
Hope this helps,
windknife
Thank you for your input again, Windknife. The formula works, but it really bogs down my computer for some reason. I am on a Mac though, using Excel 2008 for Mac.
Any other less cumbersome solutions possibly?
Thank you again.
Try this
=SUMPRODUCT(--($A$1:$A$1000=$D1),--($B$1:$B$1000=$E1))
adjust the 1000 to a resonable number!
Regards
Darren
Update 12-Nov-2010 Still job hunting!
If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.
Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!
That formula of Squiggler's is not a conditional format, if that's not obvious. Slip that into Column C and all the 1s flag the duplicates. You could then use a simple =C1>0 for the conditional formatting in D and E to light them up.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
It worked for me when I tested it as a conditional format but I had to add and if entry in D column is blank then 0...without the "if" the blank cells were also filled
Gary
you could do it with formula is option in conditional format by using
=SUMPRODUCT(--($A$1:$A$1000=$D1),--($B$1:$B$1000=$E1))>0
OOPS
try
=AND(ISTEXT($D1),ISTEXT($E1),(SUMPRODUCT(--($A$1:$A$1000=$D1),--($B$1:$B$1000=$E1))>0))
Last edited by martindwilson; 06-07-2009 at 05:51 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
The formula I gave is for conditional formatting!!!
it will evaluate to true or false!
The formula I gave is conditional, I didnt allow for the blanks but
Does!Please Login or Register to view this content.
Another alternative
It should work faster as it limits the searches to only the range in column a with a text value, so the blank check can be avoided, the only case it wont work for is if there is only a surname!Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks