+ Reply to Thread
Results 1 to 10 of 10

random answers generated from a list

  1. #1
    Registered User
    Join Date
    04-10-2010
    Location
    paris
    MS-Off Ver
    Excel 2003
    Posts
    7

    random answers generated from a list

    Hi

    Using excel 2003

    I have a list
    its 100 rows long and about 8 rows wide its numbered and formatted so differnt entries are different colours

    i want to be able to either press a button or press F9 and a random row (all 8 cell from the row of the list )is selected and appears into the cell i want at the top.

    i have the random bit working and i almost solved it by doing the lookup function but only one cell from the row ( will display) if i tell it do more i get error.
    Also the colour formatting dosnt travel to the new cell its just normal unformatted.

    please help me..i just cant seem to work it out....

    also im a noob with excel .im keen but dont know much
    Last edited by juicy fruit; 04-10-2010 at 04:18 PM.

  2. #2
    Valued Forum Contributor tony h's Avatar
    Join Date
    03-14-2005
    Location
    England: London and Lincolnshire
    Posts
    1,186

    re: random answers generated from a list

    I think you will need to attach a sample workbook to make sense of you problem


    click on the * Add Reputation if this was useful or entertaining.

  3. #3
    Registered User
    Join Date
    04-10-2010
    Location
    paris
    MS-Off Ver
    Excel 2003
    Posts
    7

    re: random answers generated from a list

    Ive uploaded my problem.

    I kinda bodged the lookup problem to show the whole row of cells but there is still a few issues.

    open my sheet and read the comment in the boxes to see what i am trying to achieve please..


    thankyou for your help.


    edit second example is how i want it to look ..it still generates random results but i have hidden all the workings so thats why i need the to get it like i said in the comment on the first sheet i attached..

    many thanks
    Attached Files Attached Files
    Last edited by juicy fruit; 04-10-2010 at 08:44 PM.

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: random answers generated from a list

    you should post workbooks without links to other workbooks
    hmm my mistake but you appear to be using analysis toolpak vba ?
    but unless the cells are coloured by some criteria you will need a vba solution to match cell colour.
    also it would help if 1-4 3-6 etc appeared in separate cells
    Last edited by martindwilson; 04-10-2010 at 09:01 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Registered User
    Join Date
    04-10-2010
    Location
    paris
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: random answers generated from a list

    yes im using analisis toolkit add-on

    the random numbers dont work when the book is opened on another pc unless you delete the extra crap that apears in the random box formula.

    can you help with my problem please.i rote in coment boxs on the spread sheet to make it simple to see what i need..thank you

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: random answers generated from a list

    see my last post

  7. #7
    Registered User
    Join Date
    04-10-2010
    Location
    paris
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: random answers generated from a list

    ok thx i read it.

    is there a way to maintain formatting on lookup then.

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: random answers generated from a list

    not without vba code. try asking in the programming forum

  9. #9
    Registered User
    Join Date
    04-10-2010
    Location
    paris
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: random answers generated from a list

    thx for your help..my luck to pick out a really difficult task that excel cant normally do.
    but thx for you help all the same.

  10. #10
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: random answers generated from a list

    Maybe a combination of named formula and conditional formatting can do the trick without VBA.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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