How do i have one Drop down list reference another drop down list

[Guest]

I have set up a form on the second sheet of a workbook. The first sheet
contains all relevent information for clients(in my case physicians). On the
second sheet i have set up a form, which will be used in the office, that has
a combo box list (i used the one available in the forms toolbar, as i do not
have the coding experience to use the ones in the control toolbox). This
list references the project name, and the cell reference is cell n1. I then
used the vlookup function to reference cell n1 and find the equivalent match
within sheet 1 using identifiers for each reference. ie project one (the
first option in the dropdown list) has an identifier of 1 within sheet one.
So if the value in N1 is 1 then all of the vlookup functions in the rest of
the form return the equivalent value for Idenfier 1 from Sheet 1. So in
essence whenever i change the Project name, all data in the form changes to
the corresponding identifier reference. However each project has different
clients with in it. SO when i choose a project is automatically returns the
first name with that identifier. My question is how do i make another combo
box which references the previous list and returns only the clients that have
that common project name. Any help is appreciated. I have visited the
contextures website which i have seen referenced here often and am throughly
confused. Thanks again and sorry for the wordiness.

 

[Guest]

First of all, in sheet1 all the records of the same identifier need to be
together in consective rows. If not already grouped together, you can do so
by sorting the identifier column.

Next is to make a dynamic name range based on cell N1.
From top menu go to Insert ---> Name ---> Define...
You can name it whatever you want, in reference box, put
=OFFSET(INDIRECT(ADDRESS(MATCH(N1,Sheet1!A:A,0),2,,,"Sheet1")),0,0,COUNTIF(Sheet1!A:A,N1),1)
This is assuming your identifier column is the first column in Sheet1, and
you're looking to return corresponding values in column 2. Modify according
to your needs.

Then make your combo box 2, with Input Range referring to your dynamic name
range. Your combo box 2 values should change according to your combo box 1
selection.

-Simon

 

[Guest]

I used the below formula, but there are still having problems. As an example:
Spot ID Identifier Clients Last Name DOB SSN
1 1 Albertsons Harris 9/5/2002 235-61-3219
2 2 JC Penny Wade 2/3/2004 123-25-4568
3 3 Sams Johnson 11/4/1998 111-55-6648
4 4 Target Avery 2/23/1978 444-65-2358
5 4 Target O'Neal 6/30/1987 654-96-8745
6 6 Wal-Mart Smith 2/5/2003 222-22-2222
7 6 Wal-Mart Jones 4/8/2006 333-22-3333
8 6 Wal-Mart Graham 8/4/1965 238-623-9764
9 9 Win Dixie O'Conner 4/5/1963 132-96-3025

On sheet two the first drop down references Clients and the input range is
Sheet1 C2:C10...this dropdown list is fine but it does have the option of
picking each of the multiple entries (is this a problem). When i make the
dynamic name range (the N1 from orinal post) like you said i reference the
cell that corresponds to the appropriate value in column 2 in sheet 1.
However, i keep getting errors. I want the second drop down to only have the
names that appear where the Client (sheet 1 column c) is the same. Any more
idea, what might i be doing wrong.

 

[Guest]

i dont understand what you want the second combo box to show?.you make the
selection 1 from the first combo box = Albertson and the rest of the data
presumably
Harris 9/5/2002 235-61-3219 shows up.What is supposed to show in the second
combobox?

 

[Guest]

The second combo box would have only the Last names that have the same Client
in common. So in the previous example, when i click on Wal-Mart in the First
Drop down, the second dropdown contains Smith, Jones, and Graham. The
problem i have first is that the first drop down contains three selections
for Wal Mart. The range input is A2:A10. How do i make the first combo box
only show unique values for the range and not duplicates?

 

[Guest]

This is better with the sample records you provided. For the named range, try:
=OFFSET(INDIRECT(ADDRESS(MATCH(Sheet1!$N$1,Sheet1!$B:$B,0),4,,,"Sheet1")),0,0,COUNTIF(Sheet1!$B:$B,Sheet1!$N$1),1)
My mistake on the previous post was not useing $ for absolute references.
Guessing wrong on what columns you had for each field of data didn't help
either.

You can't make the combo box selections with unique values if your input
range doesn't have unique values. However, you can make a unique value list
from your original list, and refer to the new list with your first combo box.

Select your Clients column
From top menu go to Data ---> Advanced Filter
Select Copy to another location
Fix List range if necessary
Pick a Copy to location
Check Unique records only check box
Change your input range for combo box 1 to the new list with unique records

Make sure you change your identifiers for Wal-Mart to 5, and Win Dixie to 6.

-Simon