formula for my lotto bonus number game

[dougers1]

Hi how can i get my sheet to check staff lotto numbers if they have the bonus number

what i want
1 staff will pick 6 numbers
2 if they have the bonus number in their picks then it will highlight it or grey it out
3 this way i can see who is closest to winning

please see attachment

[gmr4evr1]

I used CF to achieve what you wanted. See attached
dougers1-formula-for-my-lotto-bonus-number-game-lotto.xlsx

[dougers1]

Hi gmr4evr1

can you sort it so that i can add new bonus number and the old one stay highlighted. at the moment if i enter 17 then all the 17 get highlighted but if i enter new number 17 then become un highlighted- also is it possible to for a reset button i could hit once i have a winner


Thanks

[etaf]

select B2:G23
then use conditional formatting
and the formula

=B4=$K$5

[dougers1]

etaf this still only allows me to highlight the current number ,once i add a new bonus number the old one revert back to normal, i would like it to stay highlighted -- a bit like bingo i suppose

[etaf]

i was creating the spreadsheet as others answered - so did not see your later questionn

it could be setup to do different or the same colours if you added the bonus ball in cells
K5
K6
K7
etc and then clear them when you want to reset
otherwise you will need VBA - not my expertise

[dougers1]

yeah thats sound like a better way -

[etaf]

just add a rule for each you want a new entery

whats the max number ?

[dougers1]

just add a rule for each you want a new entery

whats the max number ?

Lotto numbers are 1 to 59

[etaf]

no , i meant the cells with bonus balls entered
K5,6,7,etc

[dougers1]

that would depend on as the same number could come out twice, i had a look at you CF and changed it to =COUNTIF($K$5:$K$100,B4)>0

that should cover it

Thank You

[gmr4evr1]

I've added a "Reset" button added to etaf's solution.
lotto_etaf1 V3.xlsm

[dougers1]

Thank you both this is brilliant

[gmr4evr1]

No problem.

[etaf]

you are welcome

[ItsmeArron]

Hi Guys,

i know this is an old post - but i am looking for the exact same excel solution, i have downloaded the last sheet shared by gmr4evr1 and have to say it works amazingly.
However i would need the ranges of the drawn bonus nall numbers K5, K6, K7 etc... to be a greater range.i would need it expanding to enter numbers down to around K60 as we dont knwo how many weeks each bonus bingo will go on for. the current sheet seems to stop at 12 numbers. i cant see any formulars in any of the cells so i can change it myself?
Thanks for any support.