• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Condtl formatting - Traffic light (Text)

vasim

vasim

Member
Hello Experts.....


So my boss wants to see the Traffic Lights condiotnal formatting in the report....(why did I showed him)...


Now when I input "R", "A" or "G" in the cell, instead of the text it should show the proper traffic light....


Any ideas...how to apply it on text....

VBA, Function, any trick...all accepted
 
Hi,


Under CF pick new rule and do a rule for each one


=A1="R" format to Red


=A1="A" format to Amber


=A1="G" format to Green
 
Thanks oldchippy...but that either changes the background color or font color, I want traffic signal light instead....
 
Good day Indian


In addition to oldchippys advice you could use the traffic lights in CF shapes.
 
Hi Indian,


What version of Excel are you using 2007 or above will do Traffic lights, but they are based on the cells value
 
Hi Oldchippy,


I'm using 2007...that what my concern is, I want to use Trafic Lights for text (direct R, A or G)....any ideas
 
Indian


Sorry for the confusion but I think that the traffic lights can only be used with Number, Percent, Formula or percentile .
 
Hi Indian,


Here's one way you could show your traffic lights to appear in an adjacent cell.

Assume in A1 you put either "R", "A" or "G", then in an adjoining cell B1 put this formula


=IF(A1="R",67,IF(A1="A",33,IF(A1="G",-1,"")))


Select B1 then in CF, pick the traffic lights icon. Click CF again and Manage Rules > Edit Rule. Change the type to number and insert 67 and 33 in the boxes, then tick reverse icon order.


Lastly to only get the traffic light to show in the cell B1 and not the value as well, custom format B1 to ;;;
 
Hi, Indian!


You've got another two choices:

a) convince your boss to enter -1,0,1 instead of G,A,R

b) change your boss ("sembrare un incidente")


Regards!
 
Good one SirJB7...the second option is good....


Ok so here is great progress...but still....


I defined name as follows G = 1, A = 2 & R = 3....its fine till I define G & A, but excel isnt allowing me to define R.....it shows up error.....why why why.....why only R....


or else i would have done it......
 
Hi, Indian!


I'm bad news.


Give a look at this link from Microsoft:

http://office.microsoft.com/en-us/excel-help/define-and-use-names-in-formulas-HA102749565.aspx?CTT=1


Check where it says "Learn about syntax rules for names":

NOTE You cannot use the uppercase and lowercase characters "C", "c", "R", or "r" as a defined name, because they are all used as a shorthand for selecting a row or column for the currently selected cell when you enter them in a Name or Go To text box.


Sorry.


Regards!
 
Thanks SirJB7....you at least helped me to give an explanation.....


I am making =Green, =Amber & =Red...now....thanks....


Thanks


P.S: You are not Bad News...not at all...you are worst news :):)

Just Kidding.....
 
Hi ,


I do not know your idea , so I cannot say whether it will work or not ; however , I do not see the connection between the Traffic Light icons Red , Green and Amber , and the letters R , G and A.


If your manager can enter the letters S , G and W ( for STOP , GO and WATCH OUT ) , then you can still implement the Traffic Light icons set. The fact that you cannot use the letter R need not come in the way of implementing your idea with another letter.


Narayan
 
Hi, Indian!

Glad to help and sorry for your boss choice of R, but Excel guys don't permit it.

Regards!

PS: always improving...
 
You must log in or register to reply here.
Back
Top