Thread: How can I use the result from ADDRESS in another formula

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?

Use the INDIRECT funciton.

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

HTH,
Elkar


"Bill" wrote:

> 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?

That did it, thanks,
Bill

"Elkar" wrote:

> Use the INDIRECT funciton.
>
> =INDIRECT(ADDRESS(RANDBETWEEN(2,53),1,4)
>
> HTH,
> Elkar
>
>
> "Bill" wrote:
>
> > 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?

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

"Bill" wrote:

> That did it, thanks,
> Bill
>
> "Elkar" wrote:
>
> > Use the INDIRECT funciton.
> >
> > =INDIRECT(ADDRESS(RANDBETWEEN(2,53),1,4)
> >
> > HTH,
> > Elkar
> >
> >
> > "Bill" wrote:
> >
> > > 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?

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" wrote:

> 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
>
> "Bill" wrote:
>
> > That did it, thanks,
> > Bill
> >
> > "Elkar" wrote:
> >
> > > Use the INDIRECT funciton.
> > >
> > > =INDIRECT(ADDRESS(RANDBETWEEN(2,53),1,4)
> > >
> > > HTH,
> > > Elkar
> > >
> > >
> > > "Bill" wrote:
> > >
> > > > 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?

"Bill" wrote:
> 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 D2:D53 as the randomized source.
Link/point to D2:D6 for one hand, to D7:D11 for the 2nd hand, and so on

Pressing F9 re-calcs for a fresh shuffle.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

That did it. Thank you.
Bill

"Elkar" wrote:

> 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" wrote:
>
> > 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
> >
> > "Bill" wrote:
> >
> > > That did it, thanks,
> > > Bill
> > >
> > > "Elkar" wrote:
> > >
> > > > Use the INDIRECT funciton.
> > > >
> > > > =INDIRECT(ADDRESS(RANDBETWEEN(2,53),1,4)
> > > >
> > > > HTH,
> > > > Elkar
> > > >
> > > >
> > > > "Bill" wrote:
> > > >
> > > > > 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?

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" wrote:

> "Bill" wrote:
> > 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 D2:D53 as the randomized source.
> Link/point to D2:D6 for one hand, to D7:D11 for the 2nd hand, and so on
>
> Pressing F9 re-calcs for a fresh shuffle.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

"Bill" wrote:
> 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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---