| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
comparing a cell to a block off cels
Hi!
I'm looking for a project. I'm bored! Send me an email and I'll make one for you then you can study it to see how it was done. Here's my address: xl can help at comcast period net Remove "can" and change the obvious. Biff "B4MB4M" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
comparing a cell to a block off cels
P.S.
OR.... Send me the file you currently have and I'll fix it. Biff "Biff" wrote in message ... Hi! I'm looking for a project. I'm bored! Send me an email and I'll make one for you then you can study it to see how it was done. Here's my address: xl can help at comcast period net Remove "can" and change the obvious. Biff "B4MB4M" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
comparing a cell to a block off cels
Thanx Biff for your offer, What I made is in the Mail. As I said I'm not an advanced user, so I usualy solve thing the hard way...using stuff I know...thus making it much more difficult that it actualy is. Please put an explanation in the sheet... so I can see how it's done. Thanx again B4M -- B4MB4M ------------------------------------------------------------------------ B4MB4M's Profile: http://www.excelforum.com/member.php...o&userid=19846 View this thread: http://www.excelforum.com/showthread...hreadid=541956 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
comparing a cell to a block off cels
Please put an explanation in the sheet... so I can see how it's done
Will do! Biff "B4MB4M" wrote in message ... Thanx Biff for your offer, What I made is in the Mail. As I said I'm not an advanced user, so I usualy solve thing the hard way...using stuff I know...thus making it much more difficult that it actualy is. Please put an explanation in the sheet... so I can see how it's done. Thanx again 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 |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Adding a row to worksheet does not update cell references in another. | Excel Worksheet Functions | |||
| Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
| Dates of a Day for a month & year cell formulas | Excel Discussion (Misc queries) | |||
| Comparing cell values then labeling them based on comparision | Excel Discussion (Misc queries) | |||
| cell color index comparison | New Users to Excel | |||
Linear Mode
