+ Reply to Thread
Results 1 to 13 of 13

Conditional formatting doesn't work for Drop Down List containing Custom Number Format

  1. #1
    Registered User
    Join Date
    03-14-2016
    Location
    toronto, canada
    MS-Off Ver
    2007
    Posts
    5

    Conditional formatting doesn't work for Drop Down List containing Custom Number Format

    Hi,

    I created a list containing number values and format them to text.

    I then created a drop down list for a cell using this first list.

    I want this cell format to change based on the original number selected.

    For some reason it doesn't work. I have attached the file here.

    Thanks in advance for sharing your ideas.

    Best,

    R

  2. #2
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Conditional formatting doesn't work for Drop Down List containing Custom Number Format

    Not quite sure what you're trying to achieve there. When I select from the dropdown, the list contains the custom format text strings, not the underlying numeric values, which looks like it is doing what you expect it to?

  3. #3
    Registered User
    Join Date
    03-14-2016
    Location
    toronto, canada
    MS-Off Ver
    2007
    Posts
    5

    Re: Conditional formatting doesn't work for Drop Down List containing Custom Number Format

    Hi Jason,

    Thanks for replying.

    The drop down does show formatted string, however, not interactively.

    For example: I can select M (numeric value = 0, string = "M"), it shows up ok

    Then I went on to select IC (numeric value = 0.01, string = "IC"), it still shows up as "M"

    I then went on to select EF (numeric value = 0.05, string = "EF"), it still shows up as "M".


    If I start this process with selecting "IC" first, then all other selections show up as "IC". The drop down cell doesn't seem to be updating correctly.

    Thank you.
    Robin

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditional formatting doesn't work for Drop Down List containing Custom Number Format

    Select cell C9 then go to Data Validation, Error Alert tab, deselect Show error alert after invalid data is entered and click OK. Now you can enter the values and the correct code will appear.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    03-14-2016
    Location
    toronto, canada
    MS-Off Ver
    2007
    Posts
    5

    Re: Conditional formatting doesn't work for Drop Down List containing Custom Number Format

    Hi newdoverman,

    Thanks for looking into this and thanks for trying different things.

    I tried your sheet but it doesn't work still.

    I started by selecting each options from the drop down list: "IC", "C", "EF" and "M". All worked well.

    Then I started again from the top, this time doesn't matter what I select, it shows up as "M". "M" happens to be the first rule in the conditional formatting.

    To summarize, the first iteration works great, anything after doesn't work.

    I cannot figure out how is this possible?

    Thank you.
    Robin

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditional formatting doesn't work for Drop Down List containing Custom Number Format

    What are you selecting? I have selected all the values in the drop-down list and they worked perfectly. I then entered the values from column B in the drop-down cell and again it worked perfectly. So, what cells are you selecting and not getting the results that you expect?

  7. #7
    Registered User
    Join Date
    03-14-2016
    Location
    toronto, canada
    MS-Off Ver
    2007
    Posts
    5

    Re: Conditional formatting doesn't work for Drop Down List containing Custom Number Format

    In the second and third drop down selection iteration I go through "IC", "C", "EF" and "M" again and again and it continue to show up as "M".

    Thanks newdoverman

  8. #8
    Registered User
    Join Date
    03-14-2016
    Location
    toronto, canada
    MS-Off Ver
    2007
    Posts
    5

    Re: Conditional formatting doesn't work for Drop Down List containing Custom Number Format

    Forgot to mention, if I type the values in manualy it works perfectly no matter how many times I type in the values. It's only the drop down selection (after first iteration) that doesnt' work.

    Unfortunately I need the drop down list to work because this is applied to 100 cells and I have 20 users who prefers drop down list than typing in values.

    Thanks again.

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Conditional formatting doesn't work for Drop Down List containing Custom Number Format

    I can't repeat your problem no matter how many times I repeat the process. It works perfectly every time.

  10. #10
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Conditional formatting doesn't work for Drop Down List containing Custom Number Format

    Quote Originally Posted by newdoverman View Post
    I can't repeat your problem no matter how many times I repeat the process. It works perfectly every time.
    Same here, even with the original sample sheet from post #1, with no changes made.

    I even tried changing calculation to manual on the off chance that could be the cause.

    Try downloading your sample from post #1 to a different computer and see if it works then.

  11. #11
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: Conditional formatting doesn't work for Drop Down List containing Custom Number Format

    Quote Originally Posted by jason.b75 View Post
    Same here, even with the original sample sheet from post #1, with no changes made.

    I even tried changing calculation to manual on the off chance that could be the cause.

    Try downloading your sample from post #1 to a different computer and see if it works then.

    Sample from post #1 has no colors in conditional formating for cell C9.
    I put colors and all is OK.

  12. #12
    Banned User!
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: Conditional formatting doesn't work for Drop Down List containing Custom Number Format

    Quote Originally Posted by Indi_Ra View Post
    Sample from post #1 has no colors in conditional formating for cell C9.
    I put colors and all is OK.
    That is like advising somebody to repaint their car because the engine won't start.

  13. #13
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: Conditional formatting doesn't work for Drop Down List containing Custom Number Format

    Quote Originally Posted by jason.b75 View Post
    That is like advising somebody to repaint their car because the engine won't start.
    jason.b75

    There is no need to be rude, machine translation can not be good, maybe I got it wrong ...

+ 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: 6
    Last Post: 02-28-2014, 07:12 AM
  2. Updating a pivot table with VBA from Drop down list (1st item doesn't work)
    By tray262 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-25-2013, 02:32 PM
  3. Conditional Formatting doesn't work after copy and insert
    By amarus99 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-01-2012, 02:52 PM
  4. Excel 2007 : AND doesn't work in Conditional Formatting
    By cellarir in forum Excel General
    Replies: 2
    Last Post: 03-21-2012, 06:10 PM
  5. VBA code for (un)hide columns related to drop down list doesn't work
    By maumon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-21-2010, 08:27 AM
  6. Replies: 3
    Last Post: 09-09-2009, 03:35 PM
  7. [SOLVED] Custom Time Format doesn't work for me
    By chuck in forum Excel General
    Replies: 1
    Last Post: 02-13-2005, 01:06 AM
  8. Replies: 0
    Last Post: 02-10-2005, 02: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