+ Reply to Thread
Results 1 to 16 of 16

How to reference a conditionally formatted cell(s)

  1. #1
    Registered User
    Join Date
    01-20-2008
    Location
    Sevenoaks, Kent Great Britain
    MS-Off Ver
    2010
    Posts
    54

    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
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: How to reference a conditionally formatted cell(s)

    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.

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    Re: How to reference a conditionally formatted cell(s)

    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

  4. #4
    Registered User
    Join Date
    01-20-2008
    Location
    Sevenoaks, Kent Great Britain
    MS-Off Ver
    2010
    Posts
    54

    Re: How to reference a conditionally formatted cell(s)

    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.



    Quote Originally Posted by yudlugar View Post
    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.

  5. #5
    Registered User
    Join Date
    01-20-2008
    Location
    Sevenoaks, Kent Great Britain
    MS-Off Ver
    2010
    Posts
    54

    Re: How to reference a conditionally formatted cell(s)

    Hi Sorry it doesnt seem to have read in correctly


    test data.png

    Quote Originally Posted by Pete_UK View Post
    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

  6. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: How to reference a conditionally formatted cell(s)

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

  7. #7
    Registered User
    Join Date
    01-20-2008
    Location
    Sevenoaks, Kent Great Britain
    MS-Off Ver
    2010
    Posts
    54

    Re: How to reference a conditionally formatted cell(s)

    Hi Just clicked on Con Formatt and choose duplicates.

  8. #8
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: How to reference a conditionally formatted cell(s)

    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

  9. #9
    Registered User
    Join Date
    01-20-2008
    Location
    Sevenoaks, Kent Great Britain
    MS-Off Ver
    2010
    Posts
    54

    Re: How to reference a conditionally formatted cell(s)

    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

  10. #10
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: How to reference a conditionally formatted cell(s)

    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.

  11. #11
    Registered User
    Join Date
    01-20-2008
    Location
    Sevenoaks, Kent Great Britain
    MS-Off Ver
    2010
    Posts
    54

    Re: How to reference a conditionally formatted cell(s)

    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

  12. #12
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: How to reference a conditionally formatted cell(s)

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

  13. #13
    Registered User
    Join Date
    01-20-2008
    Location
    Sevenoaks, Kent Great Britain
    MS-Off Ver
    2010
    Posts
    54

    Re: How to reference a conditionally formatted cell(s)

    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

  14. #14
    Registered User
    Join Date
    01-20-2008
    Location
    Sevenoaks, Kent Great Britain
    MS-Off Ver
    2010
    Posts
    54

    Re: How to reference a conditionally formatted cell(s)

    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 ?

  15. #15
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: How to reference a conditionally formatted cell(s)

    =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

  16. #16
    Registered User
    Join Date
    01-20-2008
    Location
    Sevenoaks, Kent Great Britain
    MS-Off Ver
    2010
    Posts
    54

    Re: How to reference a conditionally formatted cell(s)

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Need a macro to delete row based on conditionally formatted cell
    By twinklestar922 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-29-2013, 06:47 AM
  2. how to copy a cell that has been conditionally formatted
    By Dexter Stagg in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-27-2012, 05:03 PM
  3. How to count cell which are conditionally formatted?
    By parekhharsh_j in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-12-2011, 02:53 PM
  4. Reading a conditionally formatted cell
    By akronpow in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-03-2007, 03:57 PM
  5. Replies: 3
    Last Post: 01-25-2007, 06:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1