Combining Randbetween and T

**wutang44** · 02-15-2011, 02:29 AM

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** · 02-15-2011, 04:03 AM

Perhaps you mean:

Please Login or Register  to view this content.

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

**sweep** · 02-15-2011, 04:03 AM

Hi,

How about this?

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

**wutang44** · 02-16-2011, 01:54 PM

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** · 02-16-2011, 01:59 PM

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

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

**wutang44** · 02-17-2011, 03:53 AM

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** · 02-17-2011, 04:00 AM

Given the use of INDIRECT the INDEX is not really necessary

Please Login or Register  to view this content.

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** · 02-18-2011, 12:42 AM

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** · 02-18-2011, 03:51 AM

INDIRECT simply converts a String to a Range.

A very basic (unrealistic) example:

Please Login or Register  to view this content.

converts the string "A1" to an actual Range reference

Please Login or Register  to view this content.

If A1 contains "Z1" then

Please Login or Register  to view this content.

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)