Results 1 to 7 of 7

formula help: conditional format of one column based on "YES"/"NO" content of four others!

Threaded View

  1. #1
    Registered User
    Join Date
    10-19-2014
    Location
    Shoreline, Connecticut
    MS-Off Ver
    2010
    Posts
    5

    Lightbulb formula help: conditional format of one column based on "YES"/"NO" content of four others!

    Scenario

    Attempting to color code column S to match a pie chart on a separate tab named ‘Metrics’. Pie chart successful based on data below in columns J, L, N, and Q using stacked conditional formatting, one example:

    =COUNTIFS('CLOSED OUT'!J2:J164,"NO",'CLOSED OUT'!L2:L164,"NO", 'CLOSED OUT'!N2:N164,"NO",'CLOSED OUT'!Q2:Q164,"NO")




    For all rows 2-150 (row 1 is header w/ filter):

    Format cell color in column “S” based on multiple values in columns J, L, N, and Q

    IF J = “NO”, AND L = “NO”, AND N=”NO”, AND Q=”NO”, S = light blue fill
    IF J = “YES”, AND L = “NO”, AND N=”NO”, AND Q=”NO”, S = light red fill
    IF J = “YES”, AND L = “YES”, AND N=”NO”, AND Q=”NO”, S = light green fill
    IF J = “YES”, AND L = “NO”, AND N=”YES”, AND Q=”NO”, S = light purple fill



    I have tried: (below all have errors)
    =AND(J2="NO";L2="NO";N2="NO";Q2="NO")

    =AND($J$2="NO";$L$2="NO";$N$2="NO";$Q$2="NO")

    =AND($J:$J="NO";$L$L="NO";$N$N="NO";$Q$Q="NO")

    =IF(AND($J2="NO",$L2="NO",$N2="NO",Q2="NO"))

    =IF($J$2="NO"),IF($L$2="NO"),IF($N$2="NO"),IF($Q$2="NO")

    The closest I have come are the following, each do not flag as “error” but shade incorrect cells – rows 1 (header) and 3, but not row 2 or any other.
    =AND(J="NO";L="NO";N="NO";Q="NO")
    =AND($J2="NO",$L2="NO",$N2="NO",Q2="NO")


    The theoretical purpose is to track supplier items:
    Column J, decision to proceed with sample evaluation, yes or no
    Column L, sample evaluated by group test, yes or no
    Column N, sample evaluated by individual tester, yes or no
    Column Q, sample was rejected (for this formula, I am only interested in column Q = “NO” but there could be the need to expand this in the future)

    The formula will show:
    blue, = reject sample without evaluation
    (J = NO, L = NO, N=NO, Q=NO)
    red, = reject sample after initial evaluation
    (J = YES, L = NO, N=NO, Q=NO)
    green, = reject sample after initial eval AND group eval (color will be changed to light orange)
    (J = YES, L = YES, N=NO, Q=NO)
    purple, = reject sample after initial eval AND solo eval
    (J = YES, L = NO, N=YES, Q=NO)

    Future state:
    (COLOR) = approve sample after initial eval and group eval
    (J = YES, L = YES, N = NO, Q = YES)
    (COLOR) = approve sample after initial eval and solo eval
    (J = YES, L = NO, N = YES, Q = YES)
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by 10 Dollar Bill; 10-19-2014 at 11:39 PM. Reason: added workbook

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] If there is any text in column "A$" on "sheet1" then move cell to column "A$" on "sheet2"
    By ckgeary in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2013, 08:28 PM
  2. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  3. [SOLVED] Formula needed to display "Pass" or "Fail" if a column contains any values other than "yes
    By andreindy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2013, 05:49 PM
  4. Replies: 2
    Last Post: 08-17-2012, 05:10 AM
  5. Replies: 5
    Last Post: 10-12-2010, 06:46 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