+ Reply to Thread
Results 1 to 4 of 4

Conditional formatting formula bar character limit & new rule problem

  1. #1
    Registered User
    Join Date
    10-29-2015
    Location
    Milpitas, CA
    MS-Off Ver
    2011
    Posts
    8

    Conditional formatting formula bar character limit & new rule problem

    Hi all,

    I have Excel 2011 and I am trying to do conditional formatting over the cells in column V.
    I want to format the cells such that if anyone enters anything other than the specified pick list values, it get highlighted.
    I am using this formula:

    =NOT(OR($V2="G2MC - Sales",$V2="SF - Finance",$V2="G2W - Marketing", $V2="G2W - HR", $V2="SF - Business leader", $V2="SF - Finance",$V2="G2A - IT Management",$V2="G2AC - Support",$V2="G2A IT Consultant / MSP",$V2="G2C - Business Managers",$V2="G2C - IT"))

    However it's still highlighting certain values like "G2A - IT Management"
    Also I have more picklist values to be included in this formula but there seems to be a character limit to the formula.

    If I do apply a new rule to include the remaining values, I want both rules to work at the same time and not to take precedence over the other.

    Can someone please help me with this query?
    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Conditional formatting formula bar character limit & new rule problem

    The problem is that you have applied the conditional formatting to the range A:A, A1 being the first cell in the range and in the formula used you are referencing A2, so the cell is conditional formatted based on one cell below.
    Therefore A3 is being highlighted because A4 (G2A - IT Consultant / MSP) doesn't exist in your list because G2A IT Consultant / MSP is in your list and A4 is clearly has a hyphen (G2A - IT Consultant / MSP) so doesn't match with G2A IT Consultant / MSP.

    The best way to deal this is, if you have to add more criteria in future, create a Dynamic Named Range Buyer_Persona in this case by using the following formula...
    Please Login or Register  to view this content.
    I have create one in column K and if you add more criteria, the named range will include those also automatically.

    Now select column A and use the following formula to make a New Rule for conditional formatting and set the format as per your choice.
    =AND(A1<>"",COUNTIF(Buyer_Persona,A1)=0)

    For details, please refer to the attached.
    Attached Files Attached Files
    Last edited by sktneer; 10-29-2015 at 09:27 PM.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Registered User
    Join Date
    10-29-2015
    Location
    Milpitas, CA
    MS-Off Ver
    2011
    Posts
    8

    Re: Conditional formatting formula bar character limit & new rule problem

    Hello,

    Thank you so much for your help.
    Could you please guide me in terms of if I can create a dynamic range in another sheet and if so what formula I can use?
    I am unsure of how to go about this.

    As you can see I tried the same example and created a dynamic range in Sheet 4 and tried to do conditional formatting in Sheet 5.
    However it's highlighting everything even though the values "Both G2A & G2M"& "Concierge" exist.
    Attached Files Attached Files
    Last edited by dhruti85chavda; 11-09-2015 at 05:33 PM.

  4. #4
    Registered User
    Join Date
    10-29-2015
    Location
    Milpitas, CA
    MS-Off Ver
    2011
    Posts
    8

    Re: Conditional formatting formula bar character limit & new rule problem

    Quote Originally Posted by dhruti85chavda View Post
    Hello,

    Thank you so much for your help.
    Could you please guide me in terms of if I can create a dynamic range in another sheet and if so what formula I can use?
    I am unsure of how to go about this.

    As you can see I tried the same example and created a dynamic range in Sheet 4 and tried to do conditional formatting in Sheet 5.
    However it's highlighting everything even though the values "Both G2A & G2M"& "Concierge" exist.
    Actually I figured it out.
    Thanks!

+ 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. Replies: 7
    Last Post: 09-21-2015, 01:58 PM
  2. Conditional Formatting Rule with Formula Applied to Multiple Cells
    By Fabi1963 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-31-2015, 06:54 AM
  3. [SOLVED] Conditional Formatting New Rule Formula Construction
    By Androexcel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-19-2014, 02:53 PM
  4. [SOLVED] Conditional formatting of a cell in a column if character count reaches a limit
    By Pavan Renjal in forum Excel - New Users/Basics
    Replies: 13
    Last Post: 01-13-2013, 03:16 PM
  5. Replies: 3
    Last Post: 08-04-2012, 07:22 PM
  6. [SOLVED] conditional formatting new rule formula
    By wannabe_guru in forum Excel General
    Replies: 4
    Last Post: 05-01-2012, 11:56 AM
  7. 2003 version, conditional formatting, 3 rule limit
    By sunilmulay in forum Excel General
    Replies: 1
    Last Post: 12-26-2008, 11:05 PM

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