Hi. I have never written an excel formula and am a complete newbie. My end
goal is to select a winner for a drawing. All employees who made loan
referrals are entered to win a prize, and are entered for each referral made.
Some have made more than 150 referrals. I don't want to type in the names
hundreds of times for 3 dozen employees. I also hope that once all the names
are created for me, excel can do a random sort, and choose a winner.

Hi,

1. Highlight the range where you want to put the name 300 times
2. Type the name but don't press Enter
3. Press Ctrl+Enter

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rebecca" wrote:

Hi. I have never written an excel formula and am a complete newbie. My end
goal is to select a winner for a drawing. All employees who made loan
referrals are entered to win a prize, and are entered for each referral made.
Some have made more than 150 referrals. I don't want to type in the names
hundreds of times for 3 dozen employees. I also hope that once all the names
are created for me, excel can do a random sort, and choose a winner.

This works! Now is there a formula that can select one of these entries
randomly!

"Shane Devenshire" wrote:

Hi,

1. Highlight the range where you want to put the name 300 times
2. Type the name but don't press Enter
3. Press Ctrl+Enter

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rebecca" wrote:

Hi. I have never written an excel formula and am a complete newbie. My end
goal is to select a winner for a drawing. All employees who made loan
referrals are entered to win a prize, and are entered for each referral made.
Some have made more than 150 referrals. I don't want to type in the names
hundreds of times for 3 dozen employees. I also hope that once all the names
are created for me, excel can do a random sort, and choose a winner.

"Rebecca" wrote:
"Shane Devenshire" wrote:
1. Highlight the range where you want to put the name 300 times
2. Type the name but don't press Enter
3. Press Ctrl+Enter

This works! Now is there a formula that can select one of these
entries randomly!

One way:

=INDEX(B1:B1000,1+RAND()*COUNTA(B1:B1000))

But I would not structure the data that way. What if some employees come to
you with adjusted counts of referrerals? You would need to insert or delete
rows; and you might easily know by how many if the employee gives you an
updated total count instead of an incremental count.

One alternative:

1. List all employee names in B1:B36, and list the corresponding number of
referrals in C1:C36.

2. Put zero into A1, and starting with A2 and copy down, put =A1+C1. Note:
You can hide column A, if you wish.

3. Then the random selection is: =VLOOKUP(RAND()*SUM(C1:C36),A1:B36,2)

Caveats: As you will discover, the RAND() is recomputed every time you
change anything in the workbook. You might want to consider the following
UDF/

Function myRAND(Optional rng as Range) as Double
myRAND = Rnd()
End Function

You can use myRAND in the same way as RAND, namely myRAND().

Alternatively, you can use myRAND(C1:C37). Thus, the random selection is
changed whenever the number of referrals is changed and when you add or
delete rows before rows 37.

To add the UDF, press F11, click on Insert Module, then copy-and-paste the
function above into the VB editor pane that should appear.

Note: It would behoove you to also set the macro security to medium. In
Excel 2003, click on Tools Macros Security Medium, then click OK.


----- original message -----

"Rebecca" wrote in message
...
This works! Now is there a formula that can select one of these entries
randomly!

"Shane Devenshire" wrote:

Hi,

1. Highlight the range where you want to put the name 300 times
2. Type the name but don't press Enter
3. Press Ctrl+Enter

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rebecca" wrote:

Hi. I have never written an excel formula and am a complete newbie. My
end
goal is to select a winner for a drawing. All employees who made loan
referrals are entered to win a prize, and are entered for each referral
made.
Some have made more than 150 referrals. I don't want to type in the
names
hundreds of times for 3 dozen employees. I also hope that once all the
names
are created for me, excel can do a random sort, and choose a winner.

Errata....

I wrote:
=INDEX(B1:B1000,1+RAND()*COUNTA(B1:B1000))

Apparently, INDEX rounds the row index. So try the following instead:

=INDEX(B1:B1000,1+RAND()*(COUNTA(B1:B1000)-1))


----- original message -----

"JoeU2004" wrote in message
...
"Rebecca" wrote:
"Shane Devenshire" wrote:
1. Highlight the range where you want to put the name 300 times
2. Type the name but don't press Enter
3. Press Ctrl+Enter

This works! Now is there a formula that can select one of these
entries randomly!

One way:

=INDEX(B1:B1000,1+RAND()*COUNTA(B1:B1000))

But I would not structure the data that way. What if some employees come
to you with adjusted counts of referrerals? You would need to insert or
delete rows; and you might easily know by how many if the employee gives
you an updated total count instead of an incremental count.

One alternative:

1. List all employee names in B1:B36, and list the corresponding number of
referrals in C1:C36.

2. Put zero into A1, and starting with A2 and copy down, put =A1+C1.
Note: You can hide column A, if you wish.

3. Then the random selection is: =VLOOKUP(RAND()*SUM(C1:C36),A1:B36,2)

Caveats: As you will discover, the RAND() is recomputed every time you
change anything in the workbook. You might want to consider the following
UDF/

Function myRAND(Optional rng as Range) as Double
myRAND = Rnd()
End Function

You can use myRAND in the same way as RAND, namely myRAND().

Alternatively, you can use myRAND(C1:C37). Thus, the random selection is
changed whenever the number of referrals is changed and when you add or
delete rows before rows 37.

To add the UDF, press F11, click on Insert Module, then copy-and-paste
the function above into the VB editor pane that should appear.

Note: It would behoove you to also set the macro security to medium. In
Excel 2003, click on Tools Macros Security Medium, then click OK.


----- original message -----

"Rebecca" wrote in message
...
This works! Now is there a formula that can select one of these entries
randomly!

"Shane Devenshire" wrote:

Hi,

1. Highlight the range where you want to put the name 300 times
2. Type the name but don't press Enter
3. Press Ctrl+Enter

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Rebecca" wrote:

Hi. I have never written an excel formula and am a complete newbie. My
end
goal is to select a winner for a drawing. All employees who made loan
referrals are entered to win a prize, and are entered for each
referral made.
Some have made more than 150 referrals. I don't want to type in the
names
hundreds of times for 3 dozen employees. I also hope that once all the
names
are created for me, excel can do a random sort, and choose a winner.