Problem with Index/Array and Random Number Generator

[txcomposer]

I am trying to build into my spreadsheet a way to have a list of values in column D. For some reason, the code in each of the G pulls random values from column D but only for cell D19 and after. It never returns any values from cells D10-D18. What am I doing wrong in my code?

ROW/COL	D	E	F	G
10	A		01	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
11	B		02	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
12	C		03	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
13	D		04	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
14	E		05	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
15	F		06	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
16	G		07	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
17	H		08	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
18	I		09	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
19	J		10	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
20	K		11	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
21	L		12	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
22	M		13	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
23	N		14	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
24	O		15	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
25	P		16	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
26	Q		17	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
27	R		18	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
28	S		19	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
29	T		20	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
30	U		21	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
31	V		22	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
32	W		23	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
33	X		24	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
34	Y		25	{=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0)))))}
35	Z

<tbody>
</tbody>

 

[Marcelo Branco]

Welcome to Mr Excel forum

See if changing
=INDEX($D$10:$D$509,...
to
=INDEX($D:$D,...
it works.

Hope this helps

M.

 

[Fluff]

Maybe add the part in red
=INDEX($D$10:$D$509,INDEX(MODE.MULT(IF($D$10:$D$509<>{"",""},ROW($D$10:$D$509))),RANDBETWEEN(0,SUM(N(LEN($D$10:$D$509)>0))))-9)

 

[txcomposer]

Welcome to Mr Excel forum

See if changing
=INDEX($D$10:$D$509,...
to
=INDEX($D:$D,...
it works.

Hope this helps

M.

I think this is the correct answer. I'd love to know WHY it works, but it works. I have a macro that refreshes the random calculations as well and I just sat here clicking it for half a minute and it looks to be only pulling the data I want. I was afraid at first that doing $D:$D would pull things like the field header "INCIDENT #'s", but it doesn't for some reason. Great!

Thanks, Marcelo!