+ Reply to Thread
Results 1 to 4 of 4

3 Outcomes Conditional Formatting based on 4 blank/non blank cells

  1. #1
    Registered User
    Join Date
    08-14-2012
    Location
    Nyon
    MS-Off Ver
    Excel 2010
    Posts
    2

    3 Outcomes Conditional Formatting based on 4 blank/non blank cells

    Hi,

    I've been grappling with this problem but can't fathom how to do it.

    I am creating a table of departmental approval of Q&A's. 4 Different departments each need to approve each question. I'm trying to create a spreadsheet of their approval/non-approval.

    As such, I was hoping to create a table with conditional formatting to make it easier to see which ones need acting on etc.

    So I have a table with 6 columns - Question, Answer and then the 4 departmental replies.

    I would like each row to be different colours based on the reply.

    As they reply I shall say yes or no based on whether they give approval or not.

    So - if there are outstanding replies - it should be red

    If there are all the replies but some are "no"s then it should be yellow.

    And if all the cells are "yes"s it should be green.

    I've had a go at this for ages now with no success. Some help would be much appreciated.

    Edit: More complication I'm afraid. Instead of Yes/No it's going to be Date of approval or Reasons for non-approval. Thanks.
    Last edited by Supraman; 08-14-2012 at 06:21 AM.

  2. #2
    Registered User
    Join Date
    02-03-2010
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    72

    Re: 3 Outcomes Conditional Formatting based on 4 blank/non blank cells

    will this work?
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: 3 Outcomes Conditional Formatting based on 4 blank/non blank cells

    Try setting your conditional formats in this order, this assumes that the approval dates / rejection reasons are in columns A:D, you will need to edit to match your sheet. The 'Stop if true' box needs to be checked on the first 2 conditions.

    Condition 1 > Green

    Formula =COUNT($A2:$D2)=4

    Condition 2 > Yellow

    Formula =COUNTA($A2:$D4)=4

    Condition 3 > Red

    Formula =COUNTA($E2) (note that this assumes an entry in column E will flag the process as started.

  4. #4
    Registered User
    Join Date
    08-14-2012
    Location
    Nyon
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: 3 Outcomes Conditional Formatting based on 4 blank/non blank cells

    @Vishnu - Not quite right but thanks very much. It didn't work due to my Edit requiring the input to be dates and not just yes/no.

    @jason - Thanks so much, that's perfect!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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