Random only once
 

First, my experience: Some in VB programming in WORD and very basic
knowledge of Excel otherwise.

Now the problem: We want to run random drug tests on employees for a client
of ours. I've created a spreadsheet that uses the

=INDEX(A2:A16,INT(RAND()*15)+1)

Function I found here but there are two things I need to know if I can
adjust with that.

1. Theoretically this could end up being a legal document when we pull
names out of it and the employee contests the selection process. So we need
to be able to "save" the results. Problem is, if you save this document,
opening it automatically refreshes this formula and causes it to pick a new
name. Any way to stop that?

2. Out of say, 30 names in a list, we would sometimes want to select 10 or
8 or 2 of them at a time for testing, so we don't want duplicates -- else I'd
just put the formula in multiple cells. Is there any way to adjust the
formula so that it does not duplicate itself? Or perhaps...better yet, have
it post the results from each calculation into static cells elsewhere?

Random only once
 

Regarding freezing the values that are used.....
Base a pivot table on the list of random values. Since the pivot table's
cache is a snapshot of the source data, the pivot table will continue to work
with those values until you referesh the PT data. Of course, the random value
formulas will keep generating new numbers, but at least you'll be able work
with a static list.

For random numbers without duplicates, try this website:
http://www.exceltip.com/st/Selecting...s%09/1110.html

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Angyl" wrote:

First, my experience: Some in VB programming in WORD and very basic
knowledge of Excel otherwise.

Now the problem: We want to run random drug tests on employees for a client
of ours. I've created a spreadsheet that uses the

=INDEX(A2:A16,INT(RAND()*15)+1)

Function I found here but there are two things I need to know if I can
adjust with that.

1. Theoretically this could end up being a legal document when we pull
names out of it and the employee contests the selection process. So we need
to be able to "save" the results. Problem is, if you save this document,
opening it automatically refreshes this formula and causes it to pick a new
name. Any way to stop that?

2. Out of say, 30 names in a list, we would sometimes want to select 10 or
8 or 2 of them at a time for testing, so we don't want duplicates -- else I'd
just put the formula in multiple cells. Is there any way to adjust the
formula so that it does not duplicate itself? Or perhaps...better yet, have
it post the results from each calculation into static cells elsewhere?

Random only once
 

Yup, that should do it, Thanks!

"Ron Coderre" wrote:

Regarding freezing the values that are used.....
Base a pivot table on the list of random values. Since the pivot table's
cache is a snapshot of the source data, the pivot table will continue to work
with those values until you referesh the PT data. Of course, the random value
formulas will keep generating new numbers, but at least you'll be able work
with a static list.

For random numbers without duplicates, try this website:
http://www.exceltip.com/st/Selecting...s%09/1110.html

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Angyl" wrote:

First, my experience: Some in VB programming in WORD and very basic
knowledge of Excel otherwise.

Now the problem: We want to run random drug tests on employees for a client
of ours. I've created a spreadsheet that uses the

=INDEX(A2:A16,INT(RAND()*15)+1)

Function I found here but there are two things I need to know if I can
adjust with that.

1. Theoretically this could end up being a legal document when we pull
names out of it and the employee contests the selection process. So we need
to be able to "save" the results. Problem is, if you save this document,
opening it automatically refreshes this formula and causes it to pick a new
name. Any way to stop that?

2. Out of say, 30 names in a list, we would sometimes want to select 10 or
8 or 2 of them at a time for testing, so we don't want duplicates -- else I'd
just put the formula in multiple cells. Is there any way to adjust the
formula so that it does not duplicate itself? Or perhaps...better yet, have
it post the results from each calculation into static cells elsewhere?

Random only once
 

Just change the calc mode of the sheet from auto to manual:

<Tools <Options <Calculation tab,

And check "Manual".

Now, to get a new random set, just hit <F9.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Angyl" wrote in message
...
Yup, that should do it, Thanks!

"Ron Coderre" wrote:

Regarding freezing the values that are used.....
Base a pivot table on the list of random values. Since the pivot table's
cache is a snapshot of the source data, the pivot table will continue to
work
with those values until you referesh the PT data. Of course, the random
value
formulas will keep generating new numbers, but at least you'll be able
work
with a static list.

For random numbers without duplicates, try this website:
http://www.exceltip.com/st/Selecting...s%09/1110.html

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Angyl" wrote:

First, my experience: Some in VB programming in WORD and very basic
knowledge of Excel otherwise.

Now the problem: We want to run random drug tests on employees for a
client
of ours. I've created a spreadsheet that uses the

=INDEX(A2:A16,INT(RAND()*15)+1)

Function I found here but there are two things I need to know if I can
adjust with that.

1. Theoretically this could end up being a legal document when we pull
names out of it and the employee contests the selection process. So we
need
to be able to "save" the results. Problem is, if you save this
document,
opening it automatically refreshes this formula and causes it to pick a
new
name. Any way to stop that?

2. Out of say, 30 names in a list, we would sometimes want to select
10 or
8 or 2 of them at a time for testing, so we don't want duplicates --
else I'd
just put the formula in multiple cells. Is there any way to adjust the
formula so that it does not duplicate itself? Or perhaps...better yet,
have
it post the results from each calculation into static cells elsewhere?