Thread: comparing a cell to a block off cels
View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
B4MB4M
 
Posts: n/a
Default comparing a cell to a block off cels

Hy all,

I'm a bit of a newbie to the more advanced stuff of excel. So bear with
me if I do not use the correct terms.

I'm making a lotto form for my family. And I want to fill in the
numbers that are drawn, and automaticly highlight those numbers in the
players number listing.

I wanted to accomplish that with conditional formatting, but The
formula was too long so it didn't work out the way I wanted.
Using the following formula :


Code:
--------------------
IF(Stand.C5=Stand.$C$42;1;IF(Stand.C5=Stand.$D$42; 1;IF(Stand.C5=Stand.$E$42;1;IF(Stand.C5=Stand.$F$4 2;1;IF(Stand.C5=Stand.$G$42;1;IF(Stand.C5=Stand.$H $42;1;IF(Stand.C5=Stand.$C$43;1;IF(Stand.C5=Stand. $D$43;1;IF(Stand.C5=Stand.$E$43;1;IF(Stand.C5=Stan d.$F$43;1;IF(Stand.C5=Stand.$G$43;1;IF(Stand.C5=St and.$H$43;1;IF(Stand.C5=Stand.$C$44;1;IF(Stand.C5= Stand.$D$44;1;IF(Stand.C5=Stand.$E$44;1;IF(Stand.C 5=Stand.$F$44;1;IF(Stand.C5=Stand.$G$44;1;IF(Stand .C5=Stand.$H$44;1;IF(Stand.C5=Stand.$C$45;1;IF(Sta nd.C5=Stand.$D$45;1;IF(Stand.C5=Stand.$E$45;1;IF(S tand.C5=Stand.$F$45;1;IF(Stand.C5=Stand.$G$45;1;IF (Stand.C5=Stand.$H$45;1;IF(Stand.C5=Stand.$C$46;1; IF(Stand.C5=Stand.$D$46;1;IF(Stand.C5=Stand.$E$46; 1;IF(Stand.C5=Stand.$F$46;1;IF(Stand.C5=Stand.$G$4 6;1;IF(Stand.C5=Stand.$H$46;1;0))))))))))))))))))) )))))))))))........and so on....
--------------------


I know that it probably is not the best way , and so it seems...the
formula was too long.

So what I want to do :
- make a list of players.
- Each player has chosen a list 10 numbers.
- I want to compare those numbers (with conditional formatting) with
a block of numbers.
- This block of numbers being 25 x 6 numbers (each week 6 numbers are
drawn, and I wanted to make room for 25 weeks) Every week I fill out
the form with 6 new numbers, and I want the numbers in the players
list to enlarge/change colour (with conditional formatting).


The problem I'm facing is a clean way of comparing a cell to a block of
cells. The cell value should change colour/size if it matches any (or
more) of the numbers in the block....

Can anyone point me in the right direction ?

cheers
B4M


--
B4MB4M
------------------------------------------------------------------------
B4MB4M's Profile: http://www.excelforum.com/member.php...o&userid=19846
View this thread: http://www.excelforum.com/showthread...hreadid=541956

Reply With QuoteReply With Quote