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
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" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
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
Sorry - I am confused.
Please provide a sample workbook that shows the results you WANT (add them manually) instead of what is not working.
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
Try this in A1 and down on the Project Plan sheet in the V3.0 file:Formula:
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.
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
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
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks