+ Reply to Thread
Results 1 to 9 of 9

Traffic lights - Conditional Formatting

  1. #1
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    109

    Traffic lights - Conditional Formatting

    Hi,

    In the enclosed file I have got so far with Conditional Formatting in a traffic light arrangement, but I have a problem when I am trying to copy the text from column B to column A, all is fine if I use the correct traffic values of -1,0,1 but if I leave a cell blank in column B then col A still sees this cell has a '0' and then enters unwanted text.

    Any help appreciated.

    Garry
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,497

    Re: Traffic lights - Conditional Formatting

    Add another IF statement:

    =IF(B1="","",IF(B1=-1,"Not Started",IF(B1=0,"In Progress",IF(B1=1,"Done",))))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  3. #3
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: Traffic lights - Conditional Formatting

    Many thanks for your speedy response.

    This works well in the original test file and if I copy and paste to another file
    it also works but not sure if there is a cell formatting issue, as you will see from the enclosed file where the items in column B when set to '0' shows a blank.

    I can see in the cell format that the 'sample is blank but on a working cell it shows the correct text 'Not Started'

    Strange one, I have tried copy and pasting formats but still the same.

    Garry
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,497

    Re: Traffic lights - Conditional Formatting

    Sorry - I am confused.

    Please provide a sample workbook that shows the results you WANT (add them manually) instead of what is not working.

  5. #5
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    109

    Re: Traffic lights - Conditional Formatting

    Sorry.

    looks to be a formatting issue - see example in file v3.0

    File version 1.0 shows a good working file and a screen shot of the correct outcome.

    In summary -

    entering '0' in B2 ( or anywhere in col B) should show 'in progress' in A2 ( Col A) ( but shows blank !! in file v3.0)
    entering 'blank in B2 ( or anywhere in col B) should show blank - which it does.

    Garry

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    Re: Traffic lights - Conditional Formatting

    Try this in A1 and down on the Project Plan sheet in the V3.0 file:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,171

    Re: Traffic lights - Conditional Formatting

    Could this be an option for you in A1 copied down...

    =IF(ISNUMBER(B1),CHOOSE(MATCH(SIGN(B1),{-1,0,1},0),"Not Started","In Progress","Done"),"")
    HTH
    Regards, Jeff

  8. #8
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    109

    Thumbs up Re: Traffic lights - Conditional Formatting

    Hi,

    Many thanks for the last 2 replays as both of these works well and have fixed the formatting issues.

    Once again, many thanks.
    Garry

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,059

    Re: Traffic lights - Conditional Formatting

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. [SOLVED] TRAFFIC LIGHTS - Conditional Formatting
    By YellowVale46 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-09-2017, 01:03 AM
  2. Traffic Lights conditional formatting
    By mjones10 in forum Excel General
    Replies: 1
    Last Post: 06-10-2016, 12:06 PM
  3. [SOLVED] Conditional Formatting - Traffic Lights issue
    By sachins19 in forum Excel General
    Replies: 11
    Last Post: 07-03-2014, 11:19 AM
  4. Training matrix traffic lights conditional formatting
    By buju247 in forum Excel General
    Replies: 6
    Last Post: 11-29-2013, 05:44 AM
  5. Conditional Formatting- Traffic Lights - Using Formulas
    By Nikki Fox in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-21-2013, 10:50 AM
  6. [SOLVED] Formatting with traffic lights
    By marreco in forum Excel General
    Replies: 7
    Last Post: 07-13-2013, 10:08 AM
  7. Traffic Lights - Conditional Formatting
    By Moe2 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-25-2012, 01:42 AM

Tags for this Thread

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