Conditional Formatting - Traffic Lights issue

**sachins19** · 07-03-2014, 04:20 AM

Hello,

I have a conditional formatting rule to display 3 Traffic Lights when the criteria is either R, A, G. In Sheet 1, A1:A4 is the criteria range and B1:B4 is where is traffic lights appear.

Now the issue is if i copy the formula beyond B4 to B5 and if the corresponding cell A5 does not have any value the traffic lights for the entire B column goes away. Is it possible to keep the cell in B column blank (while formula applied) if no value is present in A and also ensuring the traffic lights for B column does not get distorted?

I have used MATCH formula in column B which is as follows =MATCH($A1,{"G","A","R"},0)-2

Attaching the document (Sheet 1 & Sheet 2) for reference. Please help.

Regards,
SachinTraffic Lights.xlsx

**cbatrody** · 07-03-2014, 05:03 AM

Hi,

Something like this?

Regards,
Chandra

**samba_ravi** · 07-03-2014, 05:06 AM

Conditional Format applied to the Range $B$1:$B$13 change it as $B$1:$B$4

**sachins19** · 07-03-2014, 05:22 AM

Thanks Chandra, this solved my problem.

Originally Posted by cbatrody

Hi,

Something like this?

Regards,
Chandra

**cbatrody** · 07-03-2014, 05:25 AM

You are welcome

**sachins19** · 07-03-2014, 06:00 AM

I actually have a related query now. Sorry about not having asked before.

I am trying to run an advanced filter query to display rows based on the RAG status (B11). However in column B the underlying values of traffic lights (B2:B5) is -1,0,1. I have formatted the cells (B2:B5) using custom ("Red";"Green";"Yellow").

Is there a way i could use (Red, Green and Yellow) as a data validation list in the cell B11 instead of (-1,0 and 1) to make the advanced filter functionality work?

Regards
Sachin

**cbatrody** · 07-03-2014, 06:23 AM

Please see the attached file.

**sachins19** · 07-03-2014, 06:30 AM

Just checked the file. Tried running advanced filter. It works. However when you add more rows the dropdown list (B11) will increase and does not remain unique. It will show mutilple entries for Red, blue and Yellow. However a list for data validation also does not serve the purpose. Any suggestions?

Thanks Chandra!!

**cbatrody** · 07-03-2014, 06:55 AM

Please check the attached file.

**sachins19** · 07-03-2014, 07:31 AM

I see you have removed the data validation from cell D14. In that case, the dropdown cannot be used and considering this how do you input the criteria in D14?

I do realize i am asking too many questions but i know i am very close to getting this resolved.

Regards,
Sachin

**cbatrody** · 07-03-2014, 09:21 AM

Data validation is there in D14, its connected to Sheet2. Advanced Filter is working fine for Red, Green & Yellow.

**sachins19** · 07-03-2014, 11:19 AM

Thanks Chandra!!

Conditional Formatting - Traffic Lights issue

LinkBack

Thread Tools

Rate This Thread

Display

Conditional Formatting - Traffic Lights issue

Re: Conditional Formatting - Traffic Lights issue

Re: Conditional Formatting - Traffic Lights issue

Re: Conditional Formatting - Traffic Lights issue

Re: Conditional Formatting - Traffic Lights issue

Re: Conditional Formatting - Traffic Lights issue

Re: Conditional Formatting - Traffic Lights issue

Re: Conditional Formatting - Traffic Lights issue

Re: Conditional Formatting - Traffic Lights issue

Re: Conditional Formatting - Traffic Lights issue

Re: Conditional Formatting - Traffic Lights issue

Re: Conditional Formatting - Traffic Lights issue

Thread Information

Users Browsing this Thread

Similar Threads

Training matrix traffic lights conditional formatting

Conditional Formatting- Traffic Lights - Using Formulas

[SOLVED] Formatting with traffic lights

Traffic Lights - Conditional Formatting

Traffic Lights

Bookmarks

Bookmarks

Posting Permissions