Random number and indirect problem

[lrobbo314]

I have this data...

Excel Workbook
	C	D	E
1	Last	First
2
3	Rafer	Alston	c
4	Dalibor	Bagaric	c
5	Spider	Bennett	c
6	Uwe	Blab	c
7	Orbie	Bowling	c
8	Omri	Casspi	c
9	Keon	Clark	c
10	Vonteego	Cummings	c
11	Radisav	Curcic	c
12	Rastko	Cvetkovic	c
13	Predrag	Drobnjak	c
14	Pervis	Ellison	c
15	Kyrylo	Fesenko	c
16	Wyndol	Gray	c
17	Litterial	Green	c
18	Sihugo	Green	c
19	Taurean	Green	c
20	Jrue	Holiday	c
21	Dar	Hutchins	c
22	Boag	Johnson	c
23	Askia	Jones	c
24	Arvesta	Kelly	c
25	Goo	Kennedy	c
26	Buzz	Knoblauch	c
27	Arvydas	Macijauskas	c
28	Hanno	Mottola	c
29	Emeka	Okafor	c
30	Jannero	Pargo	c
31	Zarko	Paspalj	c
32	Squint	Phares	c
33	Vitaly	Potapenko	c
34	Zeljko	Rebraca	c
35	Efthimios	Rentzias	c
36	Predrag	Savovic	c
37	Ansu	Sesay	c
38	Purvis	Short	c
39	Uros	Slokar	c
40	Bello	Snyder	c
41	Vassilis	Spanoulis	c
42	Awvee	Storey	c
43	Sun	Yue	c
44	Chelso	Tamagno	c
45	Ratko	Varda	c
46	Slavko	Vranes	c
47	Stojko	Vrankovic	c
48	Delonte	West	c
49	Qyntel	Woods	c
50	Warren	Ajax	d
51	Gary	Alcorn	d
52	Steve	Alford	d
53	Peter	Aluma	d
54	Ashraf	Amaya	d
55	Ralph	Amsden	d
56	Bob	Anderegg	d
57	Stacey	Arceneaux	d
58	Gilbert	Arenas	d
59	Paul	Arizin	d
60	Bob	Arnzen	d
61	Stan	Arnzen	d
62	Vincent	Askew	d
63	Anthony	Avent	d
64	Cedric	Ball	d
65	Mike	Bantom	d
66	Jack	Burmaster	d
67	Mike	Bytzura	d
68	Pete	Chilcutt	d
69	DeSagana	Diop	d
70	Fred	Diute	d
71	Keyon	Dooling	d
72	Craig	Ehlo	d
73	Mike	Ertel	d
74	Jack	Eskridge	d
75	Ken	Exel	d
76	Al	Ferrari	d
77	Ron	Filipek	d
78	Chris	Gatling	d
79	Gordan	Giricek	d
80	George	Glamack	d
81	Marcin	Gortat	d
82	Leo	Gottlieb	d
83	Norm	Grekin	d
84	Walt	Hazzard	d
85	John	Hummer	d
86	Tony	Jaros	d
87	Jonas	Jerebko	d
88	Bill	Jesko	d
89	Splinter	Johnson	d
90	George	Kaftan	d
91	Larry	Kenon	d
92	Bud	Koper	d
93	Tom	Kozelko	d
94	Vic	Krafft	d
95	Les	Kublic	d
96	Trajan	Langdon	d
97	Hal	Lear	d
98	Fat	Lever	d
99	Marcus	Liberty	d
100	Mark	Macon	d
101	Ted	Manakas	d
102	Kennedy	McIntosh	d
103	Don	Meineke	d
104	Steve	Mix	d
105	Dyron	Nix	d
106	Charles	Oakley	d
107	Andy	Panko	d
108	Pete	Pasco	d
109	Desmond	Penigar	d
110	Bill	Perigo	d
111	Johan	Petro	d
112	Joel	Przybilla	d
113	Dave	Quabius	d
114	Rajon	Rondo	d
115	Brian	Scalabrine	d
116	Glen	Selbo	d
117	Walter	Sharpe	d
118	John	Shumate	d
119	Zeke	Sinicola	d
120	Joe	Sotak	d
121	Gino	Sovran	d
122	Jim	Spanarkel	d
123	Art	Spoelstra	d
124	Buck	Sydnor	d
125	Brett	Szabo	d
126	Dragan	Tarlac	d
127	Ime	Udoka	d
128	Nick	Exel	d
129	Gundars	Vetra	d
130	Wang	Zhizhi	d
131	Tony	Windis	d
132	Harthorne	Wingo	d
133	Eddie	Wisbar	d
134	Tony	Zeno	d
135	George	Zidek	d
Sheet3

I am trying to write a formula which will randomly retrieve one of these names, and whether it brings up the first name or the last name is based on the value in column E (if the value is C then column C, Value of d then column D.

I am trying to use a combination of the indirect function with the int(rand()*133)+3 combo in order to get my random number. The way I am writing it as if(int(rand()*133)+3="c",indirect("E"&int(rand()*133)+3... blah blah blah, will return two different random numbers and I need them to be the same number. Any thoughts?

 

[gardnertoo]

You could put in each cell of column F the formula =RAND(), then have the formula find the largest of those. That number will move each time you recalculate, but for any given run through the formula the same cell will be the largest. Then instead of INDIRECT you could use INDEX and MATCH.

 

[tusharm]

Put the RAND() function in a cell. Then reference that cell in your formula. This way the value in the cell will change with each recalculation but your formula will refer the same value for each recalculation.

I have this data...

Excel Workbook
	C	D	E
1	Last	First	*
2	*	*	*
3	Rafer	Alston	c
4	Dalibor	Bagaric	c
5	Spider	Bennett	c
6	Uwe	Blab	c
7	Orbie	Bowling	c
8	Omri	Casspi	c
9	Keon	Clark	c
10	Vonteego	Cummings	c
11	Radisav	Curcic	c
12	Rastko	Cvetkovic	c
13	Predrag	Drobnjak	c
14	Pervis	Ellison	c
15	Kyrylo	Fesenko	c
16	Wyndol	Gray	c
17	Litterial	Green	c
18	Sihugo	Green	c
19	Taurean	Green	c
20	Jrue	Holiday	c
21	Dar	Hutchins	c
22	Boag	Johnson	c
23	Askia	Jones	c
24	Arvesta	Kelly	c
25	Goo	Kennedy	c
26	Buzz	Knoblauch	c
27	Arvydas	Macijauskas	c
28	Hanno	Mottola	c
29	Emeka	Okafor	c
30	Jannero	Pargo	c
31	Zarko	Paspalj	c
32	Squint	Phares	c
33	Vitaly	Potapenko	c
34	Zeljko	Rebraca	c
35	Efthimios	Rentzias	c
36	Predrag	Savovic	c
37	Ansu	Sesay	c
38	Purvis	Short	c
39	Uros	Slokar	c
40	Bello	Snyder	c
41	Vassilis	Spanoulis	c
42	Awvee	Storey	c
43	Sun	Yue	c
44	Chelso	Tamagno	c
45	Ratko	Varda	c
46	Slavko	Vranes	c
47	Stojko	Vrankovic	c
48	Delonte	West	c
49	Qyntel	Woods	c
50	Warren	Ajax	d
51	Gary	Alcorn	d
52	Steve	Alford	d
53	Peter	Aluma	d
54	Ashraf	Amaya	d
55	Ralph	Amsden	d
56	Bob	Anderegg	d
57	Stacey	Arceneaux	d
58	Gilbert	Arenas	d
59	Paul	Arizin	d
60	Bob	Arnzen	d
61	Stan	Arnzen	d
62	Vincent	Askew	d
63	Anthony	Avent	d
64	Cedric	Ball	d
65	Mike	Bantom	d
66	Jack	Burmaster	d
67	Mike	Bytzura	d
68	Pete	Chilcutt	d
69	DeSagana	Diop	d
70	Fred	Diute	d
71	Keyon	Dooling	d
72	Craig	Ehlo	d
73	Mike	Ertel	d
74	Jack	Eskridge	d
75	Ken	Exel	d
76	Al	Ferrari	d
77	Ron	Filipek	d
78	Chris	Gatling	d
79	Gordan	Giricek	d
80	George	Glamack	d
81	Marcin	Gortat	d
82	Leo	Gottlieb	d
83	Norm	Grekin	d
84	Walt	Hazzard	d
85	John	Hummer	d
86	Tony	Jaros	d
87	Jonas	Jerebko	d
88	Bill	Jesko	d
89	Splinter	Johnson	d
90	George	Kaftan	d
91	Larry	Kenon	d
92	Bud	Koper	d
93	Tom	Kozelko	d
94	Vic	Krafft	d
95	Les	Kublic	d
96	Trajan	Langdon	d
97	Hal	Lear	d
98	Fat	Lever	d
99	Marcus	Liberty	d
100	Mark	Macon	d
101	Ted	Manakas	d
102	Kennedy	McIntosh	d
103	Don	Meineke	d
104	Steve	Mix	d
105	Dyron	Nix	d
106	Charles	Oakley	d
107	Andy	Panko	d
108	Pete	Pasco	d
109	Desmond	Penigar	d
110	Bill	Perigo	d
111	Johan	Petro	d
112	Joel	Przybilla	d
113	Dave	Quabius	d
114	Rajon	Rondo	d
115	Brian	Scalabrine	d
116	Glen	Selbo	d
117	Walter	Sharpe	d
118	John	Shumate	d
119	Zeke	Sinicola	d
120	Joe	Sotak	d
121	Gino	Sovran	d
122	Jim	Spanarkel	d
123	Art	Spoelstra	d
124	Buck	Sydnor	d
125	Brett	Szabo	d
126	Dragan	Tarlac	d
127	Ime	Udoka	d
128	Nick	Exel	d
129	Gundars	Vetra	d
130	Wang	Zhizhi	d
131	Tony	Windis	d
132	Harthorne	Wingo	d
133	Eddie	Wisbar	d
134	Tony	Zeno	d
135	George	Zidek	d
Sheet3

I am trying to write a formula which will randomly retrieve one of these names, and whether it brings up the first name or the last name is based on the value in column E (if the value is C then column C, Value of d then column D.

I am trying to use a combination of the indirect function with the int(rand()*133)+3 combo in order to get my random number. The way I am writing it as if(int(rand()*133)+3="c",indirect("E"&int(rand()*133)+3... blah blah blah, will return two different random numbers and I need them to be the same number. Any thoughts?

 

[Peter_SSs]

Edit: I notice Tushar has posted a similar suggestion while I was composing mine. never-the-less ...

... or maybe just use a single cell to get the random number then index. Note that RANDBETWEEN requires Analysis ToolPak to be installed and activated. If not you could use a form of RAND() as you were already using to generate the number in G1.

Excel Workbook
	C	D	E	F	G
1	Last	First		Rand No	5
2				Name	Orbie
3	Rafer	Alston	c
4	Dalibor	Bagaric	c
5	Spider	Bennett	c
6	Uwe	Blab	c
7	Orbie	Bowling	c
8	Omri	Casspi	c
9	Keon	Clark	c
10	Vonteego	Cummings	c
11	Radisav	Curcic	c
Rand Name

 

[mikerickson]

How about
=INDEX(IF($E$3:$E$135="d",$D$3:$D$135,$C$3:$C$135), (COUNTA($C:$C)-2)*RAND(), 1)