Is it possible to link a cell to another random cell that is within a certain range? For example, if I have a table labeled “Cards” that has 52 cells (Let’s say it has a single column (A) and 52 rows). Then I have a cell in a different table that table that I want to = one of the 52 cells at random. So, the first time I run the cell, it might equal the 3 of clubs, but the next time it might equal the 5 of diamonds.
I am playing with setting up a table that generates a random 5-card hand. I haven’t solved for duplicates yet, I figured I’d tackle that next. ;-) I briefly thought about using the RANDBETWEEN function to replace the number in the cell reference, but can’t figure out how to insert a formula into another. Ex: =Cards::A(RANDBETWEEN 1,52).
—–
Tom McVey
There are several functions that will do this. One is INDEX. If your cards are in Table 1, A1 to A52, then you can use this in another table to get one at random:
INDEX(TABLE 1::A1:A52,RANDBETWEEN(1,52))
You can also use ROWS(TABLE 1::A) instead of 52 to get the maximum value in case 52 isn't set in stone for your use.
But if the idea is to generate a 5-card hand, that won't work. You can't just do that 5 times because you may end up with two of the same card.
So you have to do exactly what actual cards do: shuffle. In your table that represents the deck, have column A be the card values, like "2 Clubs" and so on. Then column B will use RAND() to give a random value between 0.0 and 1.0. Then sort the table by column B. Every time you want a new hand, force the cells to update so you have new RAND() values, then sort again. Then all you need to do is reference cells A1 to A5 to get the five cards "on top" of the deck.