Asterix with Conditional Formating

[adamsm]

Hi anyone,

Here's what I want to get help with.

In the active sheet's cell "K14" of I'm the range 4-10.

How could I format the Cell "J14" so that if the user writes a value other than that is withing the range; an * appears near the value.

Any help on this would be kindly appreciated.

Thanks in advance.

 

[circledchicken]

Hi,

I'm not sure I fully understand what you mean, but for example if your cells look like this (note, the * hasn't been typed into J14, it appears as a result of the formatting described below):

Sheet1

	J	K
14	2*	4-10

<tbody>
</tbody>

Excel 2010

Then the following steps should work:

• Select J14
• Apply the following formula using Conditional Formatting:
  =IF(ISNUMBER(J14),J14<>MEDIAN(J14,LEFT(K14,FIND("-",K14)-1),SUBSTITUTE(K14,LEFT(K14,FIND("-",K14)),"")),TRUE)
• Set the format to something like the following or whatever other system you like:
  0"*";-0"*";0"*";@"*"

Note: I don't think this approach is a good idea however. It would be much easier to have the range is separate cells (i.e. the 4 and 10), and apply a colour for example, rather than using the *.

 

[adamsm]

I would be happy if you could give me an example of how to work out your idea of coloring the cell.

Thanks in advance.

 

[circledchicken]

It should work with basic conditional formatting (easiest if you separate the 4 and 10 to K14 and L14 respectively). If you haven't used conditional formatting much before then the following might help:

http://www.contextures.com/xlCondFormat01.html
http://www.youtube.com/watch?v=9Dk1JoL_Pe4

If you still have issues implementing it, then post back and I'm sure someone can try to help.

 

[adamsm]

Thanks for the help and recommendation. I do appreciate it.

 

[circledchicken]

Thanks for the help and recommendation. I do appreciate it.

Your welcome!