+ Reply to Thread
Results 1 to 8 of 8

Conditional Formatting not Working

  1. #1
    Forum Contributor
    Join Date
    06-21-2006
    MS-Off Ver
    2007
    Posts
    141

    Conditional Formatting not Working

    I have a dashboard report I'm developing that will allow a user to select from a drop-down list (validation list) the type of report they would like to view. Based on the type of report they choose, I've used conditional formatting to format the cells. For instance, one cell, let's say cell A2, may need to be formatted as currency for the first report type and as a percentage for another.

    I've used the following three formulas in separate conditional formatting rules in the same cell to change the format of the cell based on the value in the validation list.

    =MATCH($B$2,Result_List,0)=1 - then the format would be currency
    =MATCH($B$2,Result_List,0)=2 - then the format would be a percentage
    =MATCH($B$2,Result_List,0)=3 - then the format would be currency

    The issue is, when I change the drop down, sometimes the format in the cell changes and sometimes it does not. I think it has something to do with the order, but am unable to determine the cause...

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Conditional Formatting not Working

    Hi Braydon16,

    These three formula are giving the occurrence number of B2 in range "Result_List" and if the occurrence is 1 or 2 or 3 the logic becomes true and conditional formatting applies.. check on this angel.. and if still face issues, upload the sample file. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), dilipandey@gmail.com

  3. #3
    Forum Contributor
    Join Date
    06-21-2006
    MS-Off Ver
    2007
    Posts
    141

    Re: Conditional Formatting not Working

    I've gone thru a few different iterations with the formula and still can't determine the root. Please see the attached the file. The drop down validation list is in cell B2 with the conditional formatting issue in C8.
    Attached Files Attached Files

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Conditional Formatting not Working

    Hi Braydon,


    See the attached file and let me know if this works...
    thanks.

    Book1(18).xlsx
    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Forum Contributor
    Join Date
    06-21-2006
    MS-Off Ver
    2007
    Posts
    141

    Re: Conditional Formatting not Working

    It's still not working. If I enter the cell (F2) and hit enter it changes, but when I use the drop down it does not.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Conditional Formatting not Working

    Hi Braydon,

    Why are you going to cell F2.. that is blank.
    I just used the drop down in B2 and when I used it, the format of C8 changed accordingly...
    Check if you have your workbook in auto calc mode... thanks.

    Regards,
    DILIPandey


    <click on below 'star' if this helps>

  7. #7
    Forum Contributor
    Join Date
    06-21-2006
    MS-Off Ver
    2007
    Posts
    141

    Re: Conditional Formatting not Working

    It's in auto-calc mode. I wasn't going into cell F2, I was using Function F2 to enter the cell. If I change the drop down to Operating Efficiency (% format in C8) and then change the drop down back to Production or Accounts Receivable, the format stays in %. It does not change to currency.

  8. #8
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: Conditional Formatting not Working

    Hi Braydon16,


    If I change the drop down to Operating Efficiency
    How are you changing that.. as it has only three fields :-
    Production Dashboard
    Efficiency Dashboard
    Accounts Receivable Dashboard


    Did you mean "Efficiency Dashboard"..?
    Yes.. I tried that and when again gone back and selected 1st or 3rd field from drop down the currently format changed form % to $..
    Hence, it is working perfectly at my end.. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ 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