Conditional Fomatting Rule not working

[Alain]

I am working on an employee attendance tracking sheet, and I have conditional formatting rules that change the color of a cell in the calendar depending on the type of leave used. The challenge I have is when a user uses more than one leave type on the same day. The conditional formatting rule applied is based on the last entry on the data sheet. What I want to happen is if there is more than one leave type used on the same day, change the cell to Black with white bold text.

On my data sheet, I added a helper column (Multiple Types), and named the range (lstDataMType) to identify the duplicates in question. I referenced this in my formula but it does not work as I expected. I have attached the sheet.

The formula I use for the data validation formula is:

=(MONTH(H9)=MONTH($C9))*(SUMPRODUCT((lstDataName=valSelectName)*(YEAR(lstDataDate)=valSelectYear)*(lstDataDate<=H9)*(lstDataDate>=H9)*(lstDataMType=1))>0)

A solution that does not use the helper column would be ideal.

Thanks

 

[oldchippy]

Hi,

Some how your sheet didn't get attached

 

[Alain]

I did get an error when I was uploading, so I tried again and the second time it looked like it was successful. Regardless the file should be attached now. Thanks to oldchippy for the heads up.

 

[Alain]

Its now working. Not sure why, all I did was move the offending rule to the top of the precedence list and now all works as it should.

Thanks

 

[Debraj]

if there is more than one leave type used on the same day, change the cell to Black with white bold text.

I have not checked all the condition.. but as per your query..
You can set preference.. accordingly.. if you want to give preference BLACK one over other.. you can move it to the top in the list..



Please check and confirm..

 

[Alain]

Thanks Debraj, as you can see by the thread, I just tried it before you posted your reply. I am not sure I understand why, but it is more efficient this way. I told excel to stop checking after this rule, which should speed things up as the data grows.

Thanks again.

 

[bobhc]

Good day Alain

With CF excel starts at the first rule and works its way down the list if the first CF rule is true, it is applied if not it moves to the next and so on. If CF is applied, then Excel will not look for other rules.

You may find this link of use

http://www.techrepublic.com/blog/wi...onditional-formatting-more-efficiently/7480/#.


.

 

[Alain]

Good day Alain

With CF excel starts at the first rule and works its way down the list if the first CF rule is true, it is applied if not it moves to the next and so on. If CF is applied, then Excel will not look for other rules.

You may find this link of use

http://www.techrepublic.com/blog/wi...onditional-formatting-more-efficiently/7480/#.


.

Thanks bobhc, this is good info.