Using Index, Indirect and Rand functions.

I have several worksheets and I'd like to pick up a random value from one of them and display it in a generic worksheet. I know how to do that if I can spell out the name of the worksheet, but when I have to read the worksheet name from a cell, I'm having problem.

For example I can do this with no problem :
=INDEX(ws3!$A$1:ws3!$A$4,INT(RAND()*4)+1,1)

where ws3 is the name of the worksheet and I know there are only 4 values stored on it in cells A1 thru A4. I can pick one of them randomly.

But if instead, the generic worksheet has a column with the worksheet names in column B, so if B2 says attrnames, in C2 I want to do the function I wrote above but taking the worksheet name from B2. I tried many things using indirect and was unable to do it.

Here is a picture of my generic worksheet

A B
1 ws1 RandomvaluepulledfromA1..A4 of ws1
2 ws7 RandomvaluepulledfromA1..A4 of ws7
3 ws3 RandomvaluepulledfromA1..A4 of ws3
4

Try this and let me know if it works:

=INDEX(indirect(B2&"!$A$1:$A$4),INT(RAND()*4)+1,1)

Originally Posted by BigBas

Try this and let me know if it works:

=INDEX(indirect(B2&"!$A$1:$A$4),INT(RAND()*4)+1,1)

Where should I close/open the double quotes ?

Sorry, I pasted the wrong formula

Try this one instead:

=INDEX(indirect(B2&"!$A$1:$A$4"),INT(RAND()*4)+1,1)

I tried
=INDEX(indirect(B3&"!"$A$1:$A$4),INT(RAND()*4)+1)
and
=INDEX(indirect("B3&"!$A$1:$A$4),INT(RAND()*4)+1) and
=INDEX(indirect("B3"&!$A$1:$A$4),INT(RAND()*4)+1) no luck. is & an "and"
if I only write indirect("b3") I do get to display the worksheet name on the cell, so I'm assuming the b3 has to be in quotes? because indirect(b3) did not work alone. I just don't know how to use the double quotes and the & sign.

That was perfect, thanks! It works

Glad I could help.

If your worksheet name might be two words, i.e. have spaces within it then you'll need to amend the formula slightly, i.e.

=INDEX(indirect("'"&B2&"'!$A$1:$A$4"),INT(RAND()*4)+1)