Conditional Formatting on Data Validation List Problem

[tdye]

I have list of 61 text items in a column on a spread sheet called Data

In another sheet, Week 7, I have set up multiple cells to populate from the list by data validation (all working fine)

These 61 text items will have 1 of 6 colors associated with them selected from the drop down list.

I have successfully setup Conditional Formatting with 6 different colors, using New Formatting Rules>Classic>Format only cells that contain>Specific text>containing> & this is where my problem is ... I've only been able to put in the text from one cell Not multiple Cells, ie

From spread sheet "Data" in cell D2 is the text Billable & in D3 is the text Partially Billable. If either of these are selected from drop down on sheet titled "Week 7" I want them to color blue but I see no way of selecting the text from more than one cell.

So I could make 61 Conditional Formatting Rules for each item in the list but that seems like a lot of work

Am I using the wrong drop down selection in the Conditional Formatting - New Formatting Rule dialog box? Should it be "Use a formula to determine which cells to format" instead?

[shirleyxls]

Yes, you need to use the "Use a formula..." option, then something like:

=AND($D2="Billable",$D3="Partially Billable")

then apply your blue formatting. In the "Manage Rules" screen you will also see Applies to - make sure this is set the whole range you want the rule to be applied on.

If you get stuck, could you upload a small sample workbook (with desensitized data)

[tdye]

Thanks shirleyxls

Still can't get it working

Will upload an attachment

[shirleyxls]

OK, I see the problem.

I've modified the formulas so they link back to your list values - that way if you change the names you won't need to modify the CF formulas. I also made the cell references relative and have only applied the formatting within the range of your table.

Seems OK, but let me know if not.

[tdye]

Shirley Thank You So Much for your help!!!

[tdye]

I'm getting color sections outside the table, perhaps I'm not understanding the cell references being relative? This is when I try it on the actual sheet, not the one your updated. So when I select an item from the dropdown, it highlighted cell is not the one selected in the dropdown but one 4 rows above and one to the left.

[tdye]

Here is a screenshot

[tdye]

It's working now, above I thought I had applied the cell range in "Applies To" in the "Manage Rules" dialog box.

Thanks Again!!

[tdye]

how to do write the formula if there are more options in the drop down menu, say 5 options ranging from D21:D25?

=OR(D6=Data!$D$21,D6=Data!$D$25)

instead of =OR what would it be?

[FDibbins]

maybe this...
=countif($D$21:$D$25,D6)>0

[tdye]

Thank s FDibbins for your suggestion. I couldn't get it to work. The data is text not numeric, not sure if that matters.

I'm really supprised that the "Edit Formatting Rule" doesn't allow a formula in the settings for "Specific text" "Containing"

I found a work around, not ideal but it works: In the Data Validated List there are 61 options that within 1 of 7 corresponding colors. So what I did was put a "1." in front of each list items that I want to be blue and a "2." for those that are orange and so on.


Still open to other suggestions that are simple & don't require work arounds. Thanks

[FDibbins]

You need to use the "Use Formula" rule for this