Bart
Member
Registered: 19th Aug 02
Location: Midsomer Norton, Bristol Avon
User status: Offline
|
Im after some help with a formula.
I have a column of information C2 - C200
Im entering information into columns A2-A200
If any of the text matches the text in C2-C200 I would like to fill the cell to the right with a red background (B2-B200).
Anyone know how this can be done?
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Condition formatting and there should be something in there that you can use as your match condition.
If not, find a suitable string function to do the match and use an IF() to give you a 1 or 0 or similar, and conditionally format on that.
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
Conditional formatting is the one here, it'll do it no problem
|
Bart
Member
Registered: 19th Aug 02
Location: Midsomer Norton, Bristol Avon
User status: Offline
|
any ideas?
Ive tried:
=EXACT(A2, C2:C210)
*edit*, just trying to get a true/false to begin with.
The problem with the above is its only comparing A2 to C2, where it should be comparing A2 to C2:C200
[Edited on 23-01-2013 by Bart]
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
I see, stick this in D2 and drag it down (hide it if you don't want to see it):
=IF(COUNTIF($C$2:$C$200,A1),"True","False")
Then do your conditional formatting based on that?
edit - the $'s are important to fix the reference to C2:C200, otherwise then you drag it down it becomes C3:C201, C4:C202 etc.
[Edited on 23-01-2013 by pow]
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
=IF(COUNTIF($C$2:$C$200,A2),"True","False")
Sorry Bartm copy the above, I've put A1 in the previous one
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
Do you want one single cell to compare to the column?
Or the each cell in the column to compare the cell adjacent to it in the other column?
Doesn't need $ as I see it.
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
quote:
Originally posted by Bart
The problem with the above is its only comparing A2 to C2, where it should be comparing A2 to C2:C200
From what I understand A2, A3, A4 etc must compare to C2:C200 - hence the $'s when you drag the formula down
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Offline
|
So you want each cell in the A column to be compared to the whole of column C?
Not sure there's a quick way to type that but that sounds to me like a hidden cell containing CONCAT(C2, C3, C4 .. C200) and compare against that.
|
pow
Premium Member
Registered: 11th Sep 06
Location: Hazlemere, Buckinghamshire
User status: Offline
|
My solution works Ian, I tested it  
|
