conditional formatting for multiple criteria

Hi, new member, so hi all, and thanks for all the advice i've had from looking at historic posts!

My problem:

I have a list of names in col A that may have new entries each month causing multiple occurrences of the same name. In column L, I indicate if there has been any contact with the customer by: letter, "L", visit "V" or no contact (blank).

I have got cond formating to show when a person has had a letter or visit in both col A nd L.

My dilema is this. When a person has had a letter, i want all the times their name appears in col A to change when an L or V is enterd against one of their names. There will still be blanks from previous months where no contact was made, which must remain blank.

I'm stuck. So far, all i can get is when there is an L, and there is more than one occurrence of the name, i can get the colour in Col A to change for the name where the L is in the same row, but not for the other times their name appears:

=(COUNTIF(A:A,$A2)>1)*($L2="L")

Any help please?

Spread sheet help.jpg

This shows what I'm looking for.

Hi, just hada few weeks off work and have come back to try to get this to work!

Any suggestions on how to get the results i need as shown in the previous post's example? tearing my hair out trying to fix this so any help is appreciated

Hi Tom,

Try this equation in the conditional formatting; applying to =$A$2:$A$19

=INDEX($A$2:$A$19,MATCH(1,IF($A$2:$A$19=A2,1,0)*IF($L$2:$L$19="L",1,0),0))=A2

Seems to be working on your sample spreadsheet not sure how it will differentiate between L's and V's though as names that have had both will both want formatting, (not sure that made sense). Maybe have L's apply fill while V's change the text colour.

Let me know how you go.

Cheers,

The formatting issue I noted earlier regarding differentiating between L's and V's can be fixed easily. Just rearrange the order of conditional formatting equations in the manage rules option; the one on the top will have priority when both apply.

You will need two conditional formatting equations for =$A$2:$A$19. One for L's and one for V's, see below:

=INDEX($A:$A,MATCH(1,IF($A:$A=A2,1,0)*IF($L:$L="V",1,0),0))=A2
=INDEX($A:$A,MATCH(1,IF($A:$A=A2,1,0)*IF($L:$L="L",1,0),0))=A2

Works fine in your sample spreadsheet although changing it to A:A and L:L made my computer run really slow due to the extra calculations; could just be my computer though.

Thanks to you all.

Guest_35, yours works exactly as i wanted, and once i'd set the range correctly, it didn't kill the computer so thanks FDibbins for that save too.

benishiryo, thanks for the efforts too, much appreciated.

Originally Posted by tom72517

Thanks to you all.

Guest_35, yours works exactly as i wanted, and once i'd set the range correctly, it didn't kill the computer so thanks FDibbins for that save too.

benishiryo, thanks for the efforts too, much appreciated.

No worries... Sometimes referencing a limited array can be an issue for those who didn't create the spreadsheet, as they don't know what the equations are doing i.e. when they insert lines or information outside of the array without realising it. If you're the only person using/updating the spreadsheet it would be fine to limit the array.