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

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?

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

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

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

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.

Originally Posted by newdoverman

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.

Originally Posted by jason.b75

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.

Originally Posted by Indi_Ra

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.

Originally Posted by jason.b75

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 ...