Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Insert Button-Pick From A List (2000)

    Assume I have a column of data in A2:A25. I would like to have a button appear when I place the mouse at A26 (and subsequent cells further down in Column A) that would let me choose from among the items listed in cells A2:A25. Any help?
    Thanks,
    Jeff

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Button-Pick From A List (2000)

    Select A2:A25, hit Insert-Name-Define, call it MyList (or similar).
    Select A26, hit Data-Validation, choose List. In the box, type "=MyList" without the quotes.
    Then copy A26 to any cells you need this list to appear in a dropdown.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Insert Button-Pick From A List (2000)

    Dreamboat,
    Works like a charm. One additional question-can you restrict what an additional column's list options are by reference to an adjacent column?
    For example, assume "mylist" is A1:A5, and consists of the colors Red, Blue, Green, Yellow and Orange as text in the respective cells A1:A5. Assume further I have a similar list in cells B1:B5 that consists of the numbers 1,2,3,4 and 5, respectively. What I would like to do is if A6 equals "Red", then I can only choose the numbers 1,2 or 3 for cell B6. In the alternative, if A6 equals "Blue", B6 is "restricted" to 3,4 or 5.
    Any help?
    Thanks,
    Jeff

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Insert Button-Pick From A List (2000)

    One other quick question-how can you create a button on, say, worksheet 2 that gets data/items from a list created on Sheet 1?

  5. #5
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Quakertown, PA, Pennsylvania, USA
    Posts
    517
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Button-Pick From A List (2000)

    Checking into your 2nd question....

    3rd question: Yes, that's why I suggest using the named range method. You HAVE to use a named range to do that. If it's on the same sheet, you can just use cell references, i.e., C1:C10

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Insert Button-Pick From A List (2000)

    I'm not sure I understand. Let's use my hypothetical-the list is contained at A2:A25 on Sheet 1. Now, suppose I want to refer to the list and enter one of the items on the list into cell A2 on Sheet 2? When I tried it, it returned an error message!

  7. #7
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Insert Button-Pick From A List (2000)

    Jeff,
    if you want entry to B6 restricted depending on the contents of A6, then make a list named the same as the corresponding entry in A6, with the allowable contents, for each of the possible entries. For example make a list named Red containing 1, 2 & 3, and another list named Blue containing 4, 5 & 6.

    Then to get the processing you want, make the Data Validation for B6 be List, with a source of
    =INDIRECT(A6)

    See if that is what you want.
    Cheers, Glenn.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Insert Button-Pick From A List (2000)

    Works fine, Glenn. Thanks.

  9. #9
    Lounger
    Join Date
    Oct 2001
    Location
    Isleworth, Gtr London, England
    Posts
    28
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Insert Button-Pick From A List (2000)

    Jeff,
    Dreamboat is right, you just refer to the defined name for your list in the Data Validation.

    Are you sure you're doing it exactly right? You should define the range A2:A25 on Sheet1 as a name, say Mylist, and then click on cell A2 on Sheet2, use menu option Data/Validation, click List ( for Allow ), and then type
    =Mylist
    as the source.

    Hope that clears things up.
    Cheers, Glenn.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Insert Button-Pick From A List (2000)

    Yes-thanks to you and Dreamboat-everything is OK.

Posting Permissions

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