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

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

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

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

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

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

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