Hi,
Suppose your first list will be in column D and the other lists in columns E:H
1 First create lists for each of the drop downs you want. For now just put
them where they are easy for you to text with.
2. In each of the column D:H create a data validation using your lists:
Here are the steps for creating a data validation list:
1. Select the range
2. Choose Data, Validation
3. From the Allow drop-down and choose List
4. In the Source box enter =M1:M10 or whatever range you choose to put the
list into.
5. For the data validations for columns E:H turn the In cell drop-down check
box off. Leave it on in column D.
A. Lists can be manually entered into the Source box: 1,2,Monday,Tuesday
(no quotes are necessary, but the entries should be separated by commas.)
B. Lists can get their data from ranges: =$A$1:$A$10 (which is generally a
better choice)
If you use a range reference, as above, the range must be on the same sheet as the data validation.
If you want to refer to a range on another sheet that range must be named and you references it as =MyListName
You can name a range by selecting it and typing the name into the Name Box
(top left side of the Formula Bar) and pressing Enter or you can use the
Insert, Name, Define command.
Now we need to add code to activate the other drop-down lists:
1. To add this code to your file, press Alt+F11,
2. In the VBAProject window, top left side, find your sheet name under your
file name and double click it.
3. Paste in or type the code below:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Isect As Range
Set Isect = Application.Intersect(Target, [D1:H1])
If Not Isect Is Nothing Then
Target.Offset(0, 1).Validation.InCellDropdown = True
Target.Offset(0, 1).Select
End If
End Sub
This is designed specifically for D1:H1, since the dropdown lists are
probably going to be on many rows your range above would reflect that.
Now when the user pick an item from the drop down list in column D the
cursor is moved to column E and the drop down arrow displayed. Similarly as
they move to the right.
To pull back information about the users pick you can put a formula in
another column, such as C, for example, using our setup C1. That formula
would be
=VLOOKUP(D1,$M$1:$P$20,3,FALSE)
The range M1
20 would contain a reference table with information about the
product they picked. For example, suppose column 3 contained the "number on
hand". The above formula would return that info. The table might looks
something like:
Item Cost On Hand Other Info
Boxes 2 2
Syringes 10 7
Tapes 11 18
Bandages 17 12
Sterile Gauze 19 10
If this helps, please click the Yes button.
cheers,
Shane Devenshire