Conditional Formatting Colors after sorting

[toongal12]

I've read a lot and I can't seem to find a solution that works for me.

My conditional formatting reads from another table. It colors correctly. But if I try to Custom Sort by color, it looses the color.

My table reads for "Animal","Date","Gender". The colors is set to color red if a there is a "Cat" and "Female". I have the conditional format to read the other table with =AND(cell="cat",other_cell="female"), which works.

So when I try to custom sort for a color, it moved the row to the top, but the color stays in the row it was previously in.

I tried adding a hidden column with "Cat" in one cell and "Female" below it. The cells moved to the top again, but lost all color overall.

 

[Joyner]

So the formula should be =AND($D17="Cat",$E17="Female") and not referencing Sheet1

 

[toongal12]

So the formula should be =AND($D17="Cat",$E17="Female") and not referencing Sheet1

I cannot do that as the values are created in Sheet1. As mentioned before, the finalized values were brought over to Sheet2 to be neat and clean

Sheet1 forms a list from an INDEX-MATCH drop down. Those results are then called/referenced/duplicated (NOT just values)/also now located on Sheet2. Saying just $D17 would assume they are located at D17 of Sheet2, which they are not.

 

[toongal12]

The values Cat and Female are also only on Sheet1. They were not brought over, only other columns were

 

[Joyner]

You will need to take some other steps then to make it work. it all goes back to what I originally suspected and said above, the CF formulas are referring to the other sheet (Sheet1) that is not being sorted, so your colors will stay where they are. So you need to bring over the "Cat" and "Female" columns, or use some other method.

 

[toongal12]

You will need to take some other steps then to make it work. it all goes back to what I originally suspected and said above, the CF formulas are referring to the other sheet (Sheet1) that is not being sorted, so your colors will stay where they are. So you need to bring over the "Cat" and "Female" columns, or use some other method.

Do you know why it can't do it if calling from another sheet?

Making new columns works now. I didn't know it had to have everything called over

 

[Joyner]

It doesn't have to have everything pulled over, but if you plan on using it for CF and then sort like you are it would have to be.

It didn't work because when you sorted Sheet2, your condition formatting formulas are referring to the other sheet, Sheet1, that is stagnant and not moving, so where ever "Cat" and "Female" are on Sheet1, that is the row that will be formatting on your Sheet2 - the color will never be sorted because the reason for the coloring (CAT and Female) are not also moving on Sheet1. I hope that all makes sense.

 

[toongal12]

It doesn't have to have everything pulled over, but if you plan on using it for CF and then sort like you are it would have to be.

It didn't work because when you sorted Sheet2, your condition formatting formulas are referring to the other sheet, Sheet1, that is stagnant and not moving, so where ever "Cat" and "Female" are on Sheet1, that is the row that will be formatting on your Sheet2 - the color will never be sorted because the reason for the coloring (CAT and Female) are not also moving on Sheet1. I hope that all makes sense.

That makes sense. I didn't even think of the other table needing to move. It was driving me crazy!

I have another question though. So if I have a bunch of formulas set to make colors, is there a way to say "and make the remaining cells green"? I figured it would probably be the last in the order of priority. Like maybe the formyua is "D17>0", to show it has a value, select all cells, and that should make the remainder be green?

 

[Joyner]

If I understand and you want all cells green except those colors by the conditional formatting, all you have to do is manually highlight the entire range and use the Fill color to format them Green.

 

[toongal12]

If I understand and you want all cells green except those colors by the conditional formatting, all you have to do is manually highlight the entire range and use the Fill color to format them Green.

I need it to be conditionally formatted in case the data changes.

 

[Joyner]

I need it to be conditionally formatted in case the data changes.

I'm not exactly sure what you mean by that, since you may need to either change the manual formatting or the CF formula if things change but if you want everything else to be green that doesn't meet your criteria then just add something like:

=AND($D17<>"Cat",$E17<>"Female") or:

=NOT(AND($D17="Cat",$E17="Female")