Sorting data that contains conditional formatting

Question

I have a range of data (5 columns by 10 rows), one column of which has conditional formatting.  The conditional formatting is NOT the same for all cells in that column.  One of the columns is gender and the rules for males and females are different.  Another column is age and when I sort my data, not by gender, but by age, the conditional formatting remains with the cell rather than moving with the data.

Is it possible to get the conditional formatting to be a relative reference, and so move with the data, rather than an absolute reference?

Answer 1

The reason is,  in your conditional formatting criteria of Male and Female is not considered. Your conditional formatting is only based on column E and that also contains 2 sets, one from E3:E7 and the other one E8:E12,  so when you sort it based on the age, then as criteria of Male and Female is not considered while giving rules, so you get wrong results.

To fix this problem, Don't use conditional formatting based on Values, instead Make 3 rules of "USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT"

1. =OR(AND(D3="male",E3<80),AND(D3="female",E3<65))    give format color as green

2. =OR(AND(D3="male",E3<100),AND(D3="female",E3<90))    give format color as yellow

3. =OR(AND(D3="male",E3<200),AND(D3="female",E3<200))    give format color as red

Please refer the file on this link

http://sdrv.ms/ZB4nCx

Answer 2

https://skydrive.live.com/#cid=5812C143E9EA1AF9

I have uploaded the file and here is the link.  I have included the rules as screen captures in the file.

Answer 3

Hi,

Upload the file to SkyDrive and share the link of the uploaded file here.  Also, state the criteria which you have used for conditional formatting.

Answer 4

see this

http://ask.metafilter.com/207012/How-can-I-force-Relative-Cell-References-into-a-Conditional-Formatting-Forumual-in-Excel

http://www.cpearson.com/excel/cformatting.htm

Unfortunately, this is not what I am looking for.  The problem is being able to sort a column that contains different conditional formats.  When I sort the column, the conditional formatting remains in the cell rather than moving with the data.  I tried to illustrate this with the example I quoted but it may have been confusing.

Answer 5

see this

http://ask.metafilter.com/207012/How-can-I-force-Relative-Cell-References-into-a-Conditional-Formatting-Forumual-in-Excel

http://www.cpearson.com/excel/cformatting.htm