Hi
I have put my lotto numbers into Excel and I have eight squares for the weeks numbers that come out. What I want to do is highlight my numbers according to draw numbers. Can this be done and how? I have put a site up with my my sheet so it would make reading this question easyier.
[Solved] need help with Excel
-
Zulu -
April 27, 2004 at 12:31 PM -
Closed
-
-
-
Use Conditional formatting.
Let your 6 regular numbers be in A2:F2
Name this range rngNosLet your 2 Supplementary numbers be in G2:H2
Name this range rngSuppsSelect a cell with a lotto number in that you want to apply a special format to
Select Format>Conditional formatting from the menubar
In the condition1 box select Formula is and enter the following formula
=NOT(ISNA(MATCH(A5,rngNos,0)))
(where A5 is the selected cell)
Add your format (i.e. highlight cell background yellow)
In the condition2 box repeat the process, with a different format, change the formula to read
=NOT(ISNA(MATCH(A5,rngSupps,0)))
(I used bright green...)Now, copy just the formats to all the other cells in the range where you enter your numbers... normal number matches will be highlighted yellow, those that match the Supp numbers will turn green..
Hope this helps - I have emailed you a sample file.
Will
-
Hi
Thanks very much for your help I think I'm almost there. I have added the Conditional Format like you said but changed the A5 to C8 like it is on my sheet but how does the format know what the nos and supps are? on my sheet now my lotto numbers are and green.Regards Steve
-
In my file, from the menubar choose
Insert>Name>Define
You will see that there are two named ranges
rngNos which referes to =lotto!$A$2:$F$2
rngSupps which refers to =lotto!$G$2:$H$2You will notice that the formulas in the conditional formatting are as follows
=NOT(ISNA(MATCH(A5,rngNos,0)))
and
=NOT(ISNA(MATCH(A5,rngSupps,0)))They are basically referring to the Named ranges above
Basically, this can be broken down like follows=MATCH(A5,rngNos,0)
If the value in A5 matches a value in rngNos (i.e. A2:F2), then it will return a number
If it does not find a match, the MATCH formula returns #NAThen we build in ISNA function
=ISNA(MATCH(A5,rngNos,0)
This returns FALSE if a match is found (because the result is not #NA)
and TRUE if no match is foundby building in the NOT function we are effectively reversing the result (like a double negative)
so =NOT(ISNA(MATCH(A5,rngNos,0)))
returns FALSE if the value in A5 does NOT match the values in A2:F2
returns TRUE if it finds a matchBy default, the conditional formatting is applied whenever the result of the formula is TRUE....
Hope this helps,
Will
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!