How to reference a conditionally formatted cell(s)

Hi I attach some sample data. I want excel to see the conditionally formatted (cells in red) (G) then check in (B) if any of the corresponding duplicate rows show "HHI" then set (H) as "OK". Is this possible? Many thanks David

What is the conditionally formatting? Use something like:
=if(and(B2="HHI",A1=C1),"OK","")
in column H, where A1=C1 is your conditional formatting test.

Why did you start a new thread? You could have carried on with this one:

http://www.excelforum.com/excel-form...tted-cell.html

Your attachment does not show any red cells, so I'm not sure what you want.

Pete

Hi the column is conditionally formatted for duplicate names so in this example the the first line address is highlighted "1 Adelaide Rd " "1 Adelaide Rd" "1 Adelaide Rd " "1 Adelaide Rd" "1 Adelaide Rd" so as there is more that one of that address i want to check if any of rows 4 to 8 contain "HHI" in column B.

Originally Posted by yudlugar

What is the conditionally formatting? Use something like:
=if(and(B2="HHI",A1=C1),"OK","")
in column H, where A1=C1 is your conditional formatting test.

Hi Sorry it doesnt seem to have read in correctly


test data.png

Originally Posted by Pete_UK

Why did you start a new thread? You could have carried on with this one:

http://www.excelforum.com/excel-form...tted-cell.html

Your attachment does not show any red cells, so I'm not sure what you want.

Pete

What is the formula you use in the conditional format condition?

Hi Just clicked on Con Formatt and choose duplicates.

Ok, so the formula you want in H2 is:
=IF(AND(B2="HHI",COUNTIF(G$2:G$4,"="&G2)>1),"OK","")
Adjust G$2:G$4 to cover the full range of data (i.e. G$2:G$1000 for 1000 rows) and then copy down column H

test data made up.png

Hi sorry to bother you again but it doesnt seem to do what i need it to do. As per the attached.

To Recap:-


I want excel to see a group of of the red cells in G say for example "1 Cowper Road" then look at colmun B to see if any of the 5 B cells has "HHI" in it. If that does occur then mark each of the 5 adjacent cells in H with OK. So as another example with "1 Adelaide Rd" none of the corresponding B cells have "HHI" so nothing returned. "1Aldridge Pk" Would return 2 OK's. What I am trying to do is produce a list of clients who do not have the Product "HHI" with us.

I hope that is a little clearer, so about not being able to explain it very well. Many thanks for your help

ok so maybe:
=if(sumproduct(if(G2=G$2:G$10,1),if(B$2:B$10="HHI",1))=1,"OK","")
it's an array formula so confirm with ctrl+shift+enter, again change the G2:G10 and B2:B10 to be the full range of data.

Hi Many thanks for comming back so quickly, could you just confirm what I have to do with this bit "it's an array formula so confirm with ctrl+shift+enter" as I not familar with this. Many thanks for your patience. David

When you type the formula into the formula bar, instead of pressing enter, hold down ctrl and shift and then press enter.

Hi i have done that but it still doesnt work I have attacted a sample file. I need it to look at column B (for "HHI") if Column G has the same first line address appearing more than once then mark H as OK for all the the same address. So "1 Michael Gaynor Close" should be ignored as it appears once only whereas 1 Cowper Road appears more than once and one of the entries in B has "HHI" so should make all of H column for "1 Cowper Road" as OK. "1 Grafton Road" is correct as although it has two entries neither B columns have "HHI". I am sorry this is not too clear for you and again really appreciate your time. David

Hi i have done that but it still doesnt work I have attacted a sample file. I need it to look at column B (for "HHI") if Column G has the same first line address appearing more than once then mark H as OK for all the the same address. So "1 Michael Gaynor Close" should be ignored as it appears once only whereas 1 Cowper Road appears more than once and one of the entries in B has "HHI" so should make all of H column for "1 Cowper Road" as OK. "1 Grafton Road" is correct as although it has two entries neither B columns have "HHI". I am sorry this is not too clear for you and again really appreciate your time. David
test dummy data1.png i cant seem to attach a file in a reply ?

=IF(AND(COUNTIF(G$2:G$4,"="&G2)>1,SUMPRODUCT(IF(G$2:G$4=G2,1),IF(B$2:B$4="HHI",1))>0),"HI","")

Again, an array formula so confirm with ctrl+shift+enter

Yee Ha it works and works really well, Thanks so much for your time and effort.