Drop Down Boxes in Cells
Posted by Adams on October 15, 2001 12:17 PM
I have a spreadsheet with two drop down lists in it (A1 and B1). The
data contained in the lists are on the far right of the spreadsheet,
however, will be hidden. The store names are in one column and the
related workrooms are in the next column, so the store names repeat
according to how many workrooms there are per store. I want one drop
down list to contain store names (A1) and I want B1 (workrooms) to
look at the store select in A1's drop down and point to the workroom
related to that store. I have 100 + stores and 4-5 workrooms per
store, so insted of have a list of 500 workrooms for the user to
choose from, I can just have workrooms that relate to the selected
store. Any help is appreciated!
Thanks
Posted by Aladin Akyurek on October 15, 2001 12:42 PM
I'd suggest adding a new worksheet and call it e.g., Lists.
Put all your store names in column A in Lists.
Select all the cells where you now have store names, go to the Name Box on the Formula Bar, and type STORES.
Enter the first workrooms corresponding to the first store name (lets say its Store1) in B from B1 on. Select all the cells where you now have the workrooms of the first sore. Give to this range the name Store1 via the Name Box.
Skip one row in B after the recently entered workrooms. Enter the workrooms of the second store, select the range newly filled in, and give it the name of the second store via the Name Box.
Repeat the proc just described to create the sublists in B for all stores that you have.
Activate A1 in the worksheet where you want the dropdown lists.
Activate Data|Validation.
Select List for Allow in the Data Validation Window.
Enter as Source:
=STORES
Click OK.
Activate B1.
Activate Data|Validation.
Select List for Allow in the Data Validation Window.
Enter as Source:
=INDIRECT(A1)
Click OK.
You can hide the worksheet Lists if you want to.
By the way, you still present a big list in A1 to the user who might not like it.
Aladin
PS. It's important that the workroom lists have exactly the same name (watch out for spelling) as the stores to which they belong.
============
I want one drop
