Conditional Formatting

[chriscbt]

Ok, I'm trying to be clever with a lottery syndicate sheet

What I have currently is a list of our lottery numbers in excel and a list of the winning numbers (we have 35 lines of lottery numbers)

I'd like to make it so that when i enter a winning lottery number in a specific cell (M11, M13, M15, M17, M19, M21) then the cells with our lottery numbers in will go red, i can't use conditional formatting as i cant use a range or more than three functions on one cell.

Can anyone help me get round this..?

Chris
"A problem is just a solution waiting to happen"

 

[NBVC]

Try this in your conditional formatting dialogue.

Choose formula is from the drop down,

enter: =CHOOSE(A1,$M$11,$M$13,$M$15,$M$17,$M$19,$M$21), where A1 one of the cells where you enter the lottery numbers, then simply Copy and Paste Special|Formats to all other cells that you want condition to apply to.

 

[chriscbt]

yeah, i just tried that but i cant get it to work.

I've used =CHOOSE(E11,$M$11,$M$13,$M$15,$M$17,$M$27,$M$21)

in the conditional formatting for cell e11.

HELP.

Regards

Chris

 

[NBVC]

Try again without the dollar signs and let me know.

Also note: Your last example include M27, your original was M19, could that be a problem?

 

[chriscbt]

Should have been 19 but without the dollar sign it just lights up red no matter what value you have in M11

 

[NBVC]

It's working perfect for me,

Your conditional format is on E11 right? And the cells you want to compare are in the M cells?

I am sure there are other ways....let me think...


You have Formula Is selected from the drop down of the CF dialogue?

If you look at the conditional formatting formula again, is it surrounding by quotes? If so, delete them

 

[NBVC]

This is more brute force but,

=OR(E11=$m$11,E11=$m$13,E11=$m$15,E11=$m$17,E11=$m$19,E11=$m$21)

 

[chriscbt]

SPOT ON...THANKS VERY MUCH