How can I use the result from ADDRESS in another formula

[Bill]

So I'm trying to randomly select a cell from a colum of cards. I used the
function ADDRESS and RANDBETWEEN to get a random cell number
=ADDRESS((RANDBETWEEN(2,53)),1,4) to get a random cell from a list in colum
A. Now, I can't use that result (random cell number) for anything. It just
displays something like A22 when I use it in another cell. I need it to
display the contents of A22 to continue my project. Any ideas?

 

[Elkar]

Use the INDIRECT funciton.

=INDIRECT(ADDRESS(RANDBETWEEN(2,53),1,4)

HTH,
Elkar

 

[Bill]

That did it, thanks,
Bill

Use the INDIRECT funciton.

=INDIRECT(ADDRESS(RANDBETWEEN(2,53),1,4)

HTH,
Elkar

 

[Bill]

So now I realize that my method has allowed for two of the same cards to be
in a hand. Any idea how to correct this?
Bill

 

[Elkar]

That does get a bit more tricky. See if this link will help you:

http://www.mcgimpsey.com/excel/udfs/randint.html

HTH,
Elkar

 

[Max]

So now I realize that my method has allowed
for two of the same cards to be in a hand.
Any idea how to correct this?

One way to get the random shuffle/deal going ..

Your source deck/items are listed within A2:A53
Put in B2: =RAND()
Copy down to B53

Then just place in say, D2:
=INDEX($A$2:$A$53,RANK(B2,$B$2:$B$53))
Copy D2 down 5 rows to get the deal for a random hand of 5 cards (no
repeats).
Or, copy D2 down all the way to D53 if you want the full deck within A2:A53
randomly shuffled. Then just use D253 as the randomized source.
Link/point to D26 for one hand, to D711 for the 2nd hand, and so on

Pressing F9 re-calcs for a fresh shuffle.

 

[Bill]

That did it. Thank you.
Bill

That does get a bit more tricky. See if this link will help you:

http://www.mcgimpsey.com/excel/udfs/randint.html

HTH,
Elkar

 

[Bill]

That was a great way too. It wouldn't work for what I needed. I need a list
of two random starting cards for a promotion that I'm doing at my store. I
will print 500 or so labels from these two random starting cards. Of course,
the two cards couldn't be the same. I did finally get it done, just in case
anyone is interested.
In colum A is the "Deck" or source. Colums B & C have the array function
=RandInt(2,53). I went to the website from Elkar
http://www.mcgimpsey.com/excel/udfs/randint.html. To get the user function I
opened TOOLS-MACRO-VISUAL BASIC EDITOR. I clicked insert new module and cut
and pasted the code. Then, I selected both B2 and C2 (I eventually selected
B2 through H2 to deal out an entire hand for fun) and typed in =RANDINT(2,53)
and pressed CTRL-SHIFT-ENTER to get an array formula. Then I used the
formula =(INDIRECT(ADDRESS(($B2),1,4))) and =(INDIRECT(ADDRESS(($C2),1,4)))
in cells D2 and F2 to get the starting hands. Then I just did a drag and
fill for 100 hands. I can now use Word and Mail Merge to print these onto a
label. When I need another 100, I can press F9 to recalculate another 100
random hands. Thanks for all the help.
Bill

 

[Max]

That was a great way too. It wouldn't work for what I needed...

No prob. Glad you got it done.
Thanks for posting back ..