+ Reply to Thread
Results 1 to 9 of 9

How can I use the result from ADDRESS in another formula

  1. #1
    Bill
    Guest

    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?

  2. #2
    Elkar
    Guest

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

    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?


  3. #3
    Bill
    Guest

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

    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?


  4. #4
    Bill
    Guest

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

    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?


  5. #5
    Elkar
    Guest

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

    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?


  6. #6
    Max
    Guest

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

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

  7. #7
    Bill
    Guest

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

    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?


  8. #8
    Bill
    Guest

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

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


  9. #9
    Max
    Guest

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1