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?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ROW/COL[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]A[/TD]
[TD][/TD]
[TD]01[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]02[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]03[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]D[/TD]
[TD][/TD]
[TD]04[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]E[/TD]
[TD][/TD]
[TD]05[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]F[/TD]
[TD][/TD]
[TD]06[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]G[/TD]
[TD][/TD]
[TD]07[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]H[/TD]
[TD][/TD]
[TD]08[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]I[/TD]
[TD][/TD]
[TD]09[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]J[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]K[/TD]
[TD][/TD]
[TD]11[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]21[/TD]
[TD]L[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]22[/TD]
[TD]M[/TD]
[TD][/TD]
[TD]13[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]23[/TD]
[TD]N[/TD]
[TD][/TD]
[TD]14[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]24[/TD]
[TD]O[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]P[/TD]
[TD][/TD]
[TD]16[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD]Q[/TD]
[TD][/TD]
[TD]17[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]R[/TD]
[TD][/TD]
[TD]18[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]28[/TD]
[TD]S[/TD]
[TD][/TD]
[TD]19[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]29[/TD]
[TD]T[/TD]
[TD][/TD]
[TD]20[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD]U[/TD]
[TD][/TD]
[TD]21[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]31[/TD]
[TD]V[/TD]
[TD][/TD]
[TD]22[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]32[/TD]
[TD]W[/TD]
[TD][/TD]
[TD]23[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]33[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]24[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]34[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]25[/TD]
[TD]{=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)))))}[/TD]
[/TR]
[TR]
[TD]35[/TD]
[TD]Z[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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!