Insert Button-Pick From A List (2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Works fine, Glenn. Thanks.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote