Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula or Function to accomplish this?
I'm hoping someone can tell me of some way to automate this process: Say I have a group of 150 numbers in rows like this (each is a different cell): Code: -------------------- 3 4 9 15 30 44 1 3 14 23 30 50 2 4 10 18 28 42 -------------------- I would like to be able to enter numbers in another group of cells and, as I enter them, have Excel highlight (or change the cell color) of any matches in the above table. So if I have 15 rows of 10 numbers each, when I enter a '5' in another group of cells, Excel will highlight all of the cells in the 15X10 group that are 5. It is similar to playing bingo. The table above represents the players' cards and the 2nd group of cells represents the draws. Any ideas? Thanks in advance! -- elcapitan ------------------------------------------------------------------------ elcapitan's Profile: http://www.excelforum.com/member.php...o&userid=31125 View this thread: http://www.excelforum.com/showthread...hreadid=507926 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula or Function to accomplish this?
If the numbers from the bingo caller are al being entered into one column it is quite easy you need to apply a conditional format to the top left cell of those you want to highlight. Select formula, if the cell was B4 and the column containing the numbers being called was h4:H23 =ISNUMBER(MATCH(B4,$H$4:$H$23,0)) And then select a format for exampl eturning the cells red Then copy, paste special format the cell to all the other cells you wish the formula to apply to Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=507926 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula or Function to accomplish this?
Excellent! Thank you! What about if the numbers being entered are in the same format as the original table? Could I make the range of numbers being called h4:m10, for example? Again, thank you for the quick reply! -- elcapitan ------------------------------------------------------------------------ elcapitan's Profile: http://www.excelforum.com/member.php...o&userid=31125 View this thread: http://www.excelforum.com/showthread...hreadid=507926 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula or Function to accomplish this?
Use conditional formatting. Say that your first table is in a1:f3 and looks
like this: 3 4 9 15 30 44 1 3 14 23 30 50 2 4 10 18 28 42 Now say the range you are inputting to is a5:f7. Highlight that entire range and go into conditional formatting. Change to forrmula is and enter this formula: =COUNTIF($A$1:$F$3, A5) 0 Choose the color you want and hit ok. Now when you type a number in the a5:f7 range that matches a number in the a1:f3 range your format should occur. Note when you use absolute referencing and when you don't. Because A5 is the first cell of your conditional format range, you use relative referencing so that the formula will change accordingly for each cell in the range. -- Kevin Vaughn "elcapitan" wrote: I'm hoping someone can tell me of some way to automate this process: Say I have a group of 150 numbers in rows like this (each is a different cell): Code: -------------------- 3 4 9 15 30 44 1 3 14 23 30 50 2 4 10 18 28 42 -------------------- I would like to be able to enter numbers in another group of cells and, as I enter them, have Excel highlight (or change the cell color) of any matches in the above table. So if I have 15 rows of 10 numbers each, when I enter a '5' in another group of cells, Excel will highlight all of the cells in the 15X10 group that are 5. It is similar to playing bingo. The table above represents the players' cards and the 2nd group of cells represents the draws. Any ideas? Thanks in advance! -- elcapitan ------------------------------------------------------------------------ elcapitan's Profile: http://www.excelforum.com/member.php...o&userid=31125 View this thread: http://www.excelforum.com/showthread...hreadid=507926 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula or Function to accomplish this?
Outstanding! Thank you all for the help! -- elcapitan ------------------------------------------------------------------------ elcapitan's Profile: http://www.excelforum.com/member.php...o&userid=31125 View this thread: http://www.excelforum.com/showthread...hreadid=507926 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula or Function to accomplish this?
You are welcome.
-- Kevin Vaughn "elcapitan" wrote: Outstanding! Thank you all for the help! -- elcapitan ------------------------------------------------------------------------ elcapitan's Profile: http://www.excelforum.com/member.php...o&userid=31125 View this thread: http://www.excelforum.com/showthread...hreadid=507926 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula or Function to accomplish this?
http://www.mcgimpsey.com/excel/timestamp.html
-- paul remove nospam for email addy! "Kevin Vaughn" wrote: You are welcome. -- Kevin Vaughn "elcapitan" wrote: Outstanding! Thank you all for the help! -- elcapitan ------------------------------------------------------------------------ elcapitan's Profile: http://www.excelforum.com/member.php...o&userid=31125 View this thread: http://www.excelforum.com/showthread...hreadid=507926 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Price function difference in Output formula vis a vis Manual Calculation | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
function CELL() to return the formula in the referenced cell | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Function Formula is displayed not results | Excel Worksheet Functions |