Combining Randbetween and T

[wutang44]

I'm trying to take a column of strings and call them such that a random string is displayed every time the sheet is recalculated. The easiest way I figured to do that was with a combination T (repeat string) and Randbetween (random number in list)... the following was the formula I tried to use:

=T(Names!B(randbetween(2, 17)))

Where B(randbetween) was intended to call a particular cell, and Names! is obviously the other sheet in the workbook.

I run into two large errors: the first, and more annoying, is that it refuses to allow it based on the fact that the value will change every time it's calculated; this was intentional, I don't know why it won't allow it. More importantly, though, it doesn't like the use of the B(randbetween) to call a cell; it Likes B2, but won't accept B(2) or anything else in parentheses or quotes.

If you could please help me find an alternate solution, I'd appreciate it.

[DonkeyOte]

Perhaps you mean:

=T(INDEX(Names!B:B,RANDBETWEEN(2,17)))

You don't really need T if no cell in Names!B2:B17 is ever blank

[sweep]

Hi,

How about this?

=INDIRECT("Names!B"&RANDBETWEEN(2,17))

[wutang44]

Thanks for the help! One final question, then, before I call it solved... how do I take the Sheet name referenced (in this case, Names!) and make it reference the string in a separate cell instead? For example, if I wanted to pick names from one sheet, then another, I wouldn't have to change the formula, only the cell which contains the string that matches the appropriate sheet name.

[brokenbiscuits]

Assuming the cell which contains the string was A1, would this work?

=T(INDEX(indirect(A1&"!B:B"),RANDBETWEEN(2,17)))

[wutang44]

Thanks for the last one, that just about did the trick... I had to modify it a little, because I also needed that same effect to occur in the randbetween, the result of which looked like:

=T(index(indirect(A1&"!B:B"), randbetween(1, indirect(A1&"!B2"))))

Where B2 is a counta for a row in a separate sheet... it seems to work well. If anyone has a better way of doing that, I'm all ears, otherwise I think we can count this one as Solved.

[DonkeyOte]

Given the use of INDIRECT the INDEX is not really necessary

=T(INDIRECT("'"&A1&"'!B"&RANDBETWEEN(1,INDIRECT("'"&A1&"'!B2"))))

and reiterating earlier point - the T is only really required if the values being retrieved can be blank on occasion and you wish to display Null rather than 0 on these occasions.

[wutang44]

Works for me... I understand the last answer a little less, but it works and it's cleaner, so there you have it... unless anyone else has anything they want to add, I'm good.

[DonkeyOte]

INDIRECT simply converts a String to a Range.

A very basic (unrealistic) example:

=INDIRECT("A1")

converts the string "A1" to an actual Range reference

=A1

If A1 contains "Z1" then

=INDIRECT(A1) -> =INDIRECT("Z1") -> =Z1

So in essence you're simply building a String which you can subsequently convert to Range
(in your case you're using 2 INDIRECT the 2nd of which is used to establish the upper bound boundary of the RANDBETWEEN for use in the 1st)

Both INDIRECT & RANDBETWEEN are Volatile.

Generally speaking use of INDIRECT is only really necessary where either:

a) the precedent sheet object is variable (the case here)

or

b) the precedent sheet object / range is physically removed & replaced

It's perhaps also worth adding that INDIRECT will only work with open files (ie references to other closed files from an INDIRECT call would generate #REF! errors)