Ok, this is much more complicated than my other suggestion but it does
exactly what you want. I'll include a link to a sample file so that after
you read the setup you can see how it works before you attempt to recreate
it.

This method uses an event macro and circular reference formulas. You'll have
to enable macros for it to work and set iteration.

Do this first:

Goto the menu ToolsOptionsCalculation tab
Select Iteration
Set maximum iterations to 1
OK out

A2:A11 = list of nmes
F2:F11 = same list of names

Set conditional formatting to highlight the names that are used:

Select the range A2:A11
Goto the menu FormatConditional Formatting
Formula Is: =COUNTIF($C$2:$C$11,A2)
Click the Format button
Select the Patterns tab
Select a fill color of your choice
OK out

Select the range F2:F11
Goto the menu FormatConditional Formatting
Formula Is: =COUNTIF(D$2:D$11,F2)
Click the Format button
Select the Patterns tab
Select a fill color of your choice
OK out

To eliminate potential problems if someone types in their name and makes a
spelling error I used a drop down list so they can simply select their name
from the list. We need a special formula to generate the names that will
populate the list. I put this list off screen in an out of sight location.
So, enter this array formula** in O2 and copy down to O11:

=INDEX(A$2:A$11,SMALL(IF(ISNA(MATCH(A$2:A$11,C$2:C $11,0)),ROW(A$2:A$11)-MIN(ROW(A$2:A$11))+1),ROWS($1:1)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Now, setup the drop down list:

Select the range C2:C11
Goto the menu DataValidation
Allow: List
Source: =OFFSET(O$2,,,SUM(--ISTEXT(O$2:O$11)))
OK out

Enter this array formula** in cell D2 and copy down to D11:

=IF(C2="","",INDEX(A$2:A$11,MATCH(MAX(IF(A$2:A$11< C2,N$2:N$11)),N$2:N$11,0)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Enter this formula in cell N2 and copy down to N11:

=IF(COUNTIF(D$2:D$11,A2),"",RAND())

Now, install the event macro:

Select the sheet tab and right click
Select View Code
Paste the code below into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("C2:C11")) Is Nothing Then Exit Sub
On Error GoTo enditall
Application.EnableEvents = False
If Target.Value < "" Then
With Target.Offset(0, 1)
.Value = .Value
End With
End If
enditall:
Application.EnableEvents = True
End Sub

What this code does is when a selection is made from the drop down list in
C2:C11 it converts the formula in the corresponding cell in D2:D11 to a
constant. This keeps the names from changing because we're using volatile
RAND() functions. The only drawback to this is that once you've gone through
the entire list all the formulas are gone. If you want to do this again then
you'd have to clear the data from C2:D11 and re-enter the formulas in
D2:D11.

This code is set to the range C2:D11. If you want to use a different range
then you'd have to change this line to reflect your actual range.

If Intersect(Target, Me.Range("C2:C11")) Is Nothing Then Exit Sub

Now you can exit the VBA editor and return back to Excel. Click the top "X"
to close the window.

OK, now you're ready to try it out. Select cell C2 and select a name.
Everytime you select a cell and select a name that name is removed from the
drop down list so that by the time you select the last cell there is only
one name available to select.

I put an "extra" copy of the formula for column D in cell P2. Remember some
of these formulas are array formulas**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Here's the link to the sample file:

Grab Bag.xls 24 kb

http://cjoint.com/?letiWtlj4S

This seems way too complicated for the "simple" task at hand, but there you
go!


--
Biff
Microsoft Excel MVP


"Doebaby" wrote in message
...
Hi Myrna,

Yes, I could us the orginal formula given. It does work fine but then I
would be the one knowing all who match's to who, even my own name. I
didn't
realize how complicated it would be. In the end, if I don't receive
anymore
ideas. I will have to do something and will probably use the orginal. I
still have some time and T.Valko expressed he might try to find something
that may work. So until then, I will wait and see. Thank you for
responding.

"Myrna Larson" wrote:

On Fri, 2 Nov 2007 17:26:54 -0400, "T. Valko"
wrote:

Probably, but it'll be more complicated than what I suggested already.
And,
doing it that way it's not truly random. If you have 10 people then only
5
of them get to enter their name. So, now you have to have some process
to
see who gets to enter their name! <g

Couldn't you generate the list once, as you originally described, pairing
each
person with a randomly chosen member of the group, convert the results to
a
fixed list, then when the person types their name in, it looks that name
up in
column A and returns the name from column B?