If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dropdown box display only data dependent on another dropdown box?
I would like to create on dropdown box with the state and then a second drop
down box display only the counties for the state that was chosen in the first dropdown box. Is that possible? Thanks, Chirs |
#2
|
|||
|
|||
Dropdown box display only data dependent on another dropdown box?
Since no data layout is given, I am assuming that you have lists of counties
for each state. I will also assume that the state dropdown box is the 2 letter code for the state. State drop down box (cell A1) Data|Validation, Allow: List, Source: =StateList County drop down box (cell A2) Data|Validation, Allow: List, Source: =INDIRECT("CountyList"&A1) If this isn't what you are looking for, please provide more details as to how your data is laid out. -- John C "Chris" wrote: I would like to create on dropdown box with the state and then a second drop down box display only the counties for the state that was chosen in the first dropdown box. Is that possible? Thanks, Chirs |
#3
|
|||
|
|||
Dropdown box display only data dependent on another dropdown b
John,
In column A I have each state listed for each county in column b. i.e. A B TX Dallas TX Denton TX Fannin I can't seem to get the drop down box to display the state only once and i also can't get the indirect statement to work. Thank you in advance for any help you can provide. Chris "John C" wrote: Since no data layout is given, I am assuming that you have lists of counties for each state. I will also assume that the state dropdown box is the 2 letter code for the state. State drop down box (cell A1) Data|Validation, Allow: List, Source: =StateList County drop down box (cell A2) Data|Validation, Allow: List, Source: =INDIRECT("CountyList"&A1) If this isn't what you are looking for, please provide more details as to how your data is laid out. -- John C "Chris" wrote: I would like to create on dropdown box with the state and then a second drop down box display only the counties for the state that was chosen in the first dropdown box. Is that possible? Thanks, Chirs |
#4
|
|||
|
|||
Dropdown box display only data dependent on another dropdown b
Have a look at Debra Dalgleish's site for further detailed instruction
on how to achieve this: http://www.contextures.com/xlDataVal02.html Hope this helps. Pete On Aug 5, 3:16*pm, Chris wrote: John, In column A I have each state listed for each county in column b. i.e. A * * * * * * * * * * * B TX * * * * * * * * * * Dallas TX * * * * * * * * * * Denton TX * * * * * * * * * * Fannin I can't seem to get the drop down box to display the state only once and i also can't get the indirect statement to work. *Thank you in advance for any help you can provide. Chris "John C" wrote: Since no data layout is given, I am assuming that you have lists of counties for each state. I will also assume that the state dropdown box is the 2 letter code for the state. State drop down box (cell A1) Data|Validation, Allow: List, Source: =StateList County drop down box (cell A2) Data|Validation, Allow: List, Source: =INDIRECT("CountyList"&A1) If this isn't what you are looking for, please provide more details as to how your data is laid out. -- John C "Chris" wrote: I would like to create on dropdown box with the state and then a second drop down box display only the counties for the state that was chosen in the first dropdown box. *Is that possible? Thanks, Chirs- Hide quoted text - - Show quoted text - |
#5
|
|||
|
|||
Dropdown box display only data dependent on another dropdown b
Non-VBA solution....
Insert 2 columns before column A, so now your statelist is column C, and your county list is column D. A2: =IF(COUNTIF($C$2:$C2,C2)=1,MAX($A$1:A1)+1,"") B2: =IF(C2=myState,MAX($B$1:B1)+1,"") Copy A2 & B2 formulas down to bottom of data set. E2: =IF(ROW()-1MAX($A:$A),"",VLOOKUP(ROW()-1,$A$2:$C$1000,3,FALSE)) F2: =IF(ROW()-1MAX($B:$B),"",VLOOKUP(ROW()-1,$B$2:$D$1000,3,FALSE)) I copied E2 down to E51, and F2 down to F101. Not sure if you have all 50 states, or if you are including territories, not sure if any state has more than 100 counties, expand or modify the variables below as needed, and where you need the formulas in E and F above as needed. Then, in my example, I have named the cell that your drop down box for your state is as myState. I had to define two other variables, State List and County List Insert|Name|Define: StateList: =OFFSET(Sheet1!$E$2,0,0,50-COUNTBLANK(Sheet1!$E$2:$E$51),1) CountyList: =OFFSET(Sheet1!$F$2,0,0,100-COUNTBLANK(Sheet1!$F$2:$F$101),1) Using Data Validation for the drop down box for your state Data|Validation, Allow: List, Source: =StateList The drop down box for your count Data|Validation, Allow: List, Source: =CountyList -- John C "Chris" wrote: John, In column A I have each state listed for each county in column b. i.e. A B TX Dallas TX Denton TX Fannin I can't seem to get the drop down box to display the state only once and i also can't get the indirect statement to work. Thank you in advance for any help you can provide. Chris "John C" wrote: Since no data layout is given, I am assuming that you have lists of counties for each state. I will also assume that the state dropdown box is the 2 letter code for the state. State drop down box (cell A1) Data|Validation, Allow: List, Source: =StateList County drop down box (cell A2) Data|Validation, Allow: List, Source: =INDIRECT("CountyList"&A1) If this isn't what you are looking for, please provide more details as to how your data is laid out. -- John C "Chris" wrote: I would like to create on dropdown box with the state and then a second drop down box display only the counties for the state that was chosen in the first dropdown box. Is that possible? Thanks, Chirs |
#6
|
|||
|
|||
Dropdown box display only data dependent on another dropdown b
John,
So far so good, but I get a name error message in column b and f. I know is has to do with your formlua below referencing "myState". What exactly is myState and how do I name/assign it to what I believe is the range you are setting? "John C" wrote: Non-VBA solution.... Insert 2 columns before column A, so now your statelist is column C, and your county list is column D. A2: =IF(COUNTIF($C$2:$C2,C2)=1,MAX($A$1:A1)+1,"") B2: =IF(C2=myState,MAX($B$1:B1)+1,"") Copy A2 & B2 formulas down to bottom of data set. E2: =IF(ROW()-1MAX($A:$A),"",VLOOKUP(ROW()-1,$A$2:$C$1000,3,FALSE)) F2: =IF(ROW()-1MAX($B:$B),"",VLOOKUP(ROW()-1,$B$2:$D$1000,3,FALSE)) I copied E2 down to E51, and F2 down to F101. Not sure if you have all 50 states, or if you are including territories, not sure if any state has more than 100 counties, expand or modify the variables below as needed, and where you need the formulas in E and F above as needed. Then, in my example, I have named the cell that your drop down box for your state is as myState. I had to define two other variables, State List and County List Insert|Name|Define: StateList: =OFFSET(Sheet1!$E$2,0,0,50-COUNTBLANK(Sheet1!$E$2:$E$51),1) CountyList: =OFFSET(Sheet1!$F$2,0,0,100-COUNTBLANK(Sheet1!$F$2:$F$101),1) Using Data Validation for the drop down box for your state Data|Validation, Allow: List, Source: =StateList The drop down box for your count Data|Validation, Allow: List, Source: =CountyList -- John C "Chris" wrote: John, In column A I have each state listed for each county in column b. i.e. A B TX Dallas TX Denton TX Fannin I can't seem to get the drop down box to display the state only once and i also can't get the indirect statement to work. Thank you in advance for any help you can provide. Chris "John C" wrote: Since no data layout is given, I am assuming that you have lists of counties for each state. I will also assume that the state dropdown box is the 2 letter code for the state. State drop down box (cell A1) Data|Validation, Allow: List, Source: =StateList County drop down box (cell A2) Data|Validation, Allow: List, Source: =INDIRECT("CountyList"&A1) If this isn't what you are looking for, please provide more details as to how your data is laid out. -- John C "Chris" wrote: I would like to create on dropdown box with the state and then a second drop down box display only the counties for the state that was chosen in the first dropdown box. Is that possible? Thanks, Chirs |
#7
|
|||
|
|||
Dropdown box display only data dependent on another dropdown b
Then, in my example, I have named the cell that your drop down box for your
state is as myState Click on the cell that has the drop down box for your state list. Click on the Name box to the left of the formula bar (should be displaying the current cell), and then type in myState. Or, if you prefer, assume the cell that your drop down box for states is cell B2, you could replace all references to myState to $B$2. -- John C "Chris" wrote: John, So far so good, but I get a name error message in column b and f. I know is has to do with your formlua below referencing "myState". What exactly is myState and how do I name/assign it to what I believe is the range you are setting? "John C" wrote: Non-VBA solution.... Insert 2 columns before column A, so now your statelist is column C, and your county list is column D. A2: =IF(COUNTIF($C$2:$C2,C2)=1,MAX($A$1:A1)+1,"") B2: =IF(C2=myState,MAX($B$1:B1)+1,"") Copy A2 & B2 formulas down to bottom of data set. E2: =IF(ROW()-1MAX($A:$A),"",VLOOKUP(ROW()-1,$A$2:$C$1000,3,FALSE)) F2: =IF(ROW()-1MAX($B:$B),"",VLOOKUP(ROW()-1,$B$2:$D$1000,3,FALSE)) I copied E2 down to E51, and F2 down to F101. Not sure if you have all 50 states, or if you are including territories, not sure if any state has more than 100 counties, expand or modify the variables below as needed, and where you need the formulas in E and F above as needed. Then, in my example, I have named the cell that your drop down box for your state is as myState. I had to define two other variables, State List and County List Insert|Name|Define: StateList: =OFFSET(Sheet1!$E$2,0,0,50-COUNTBLANK(Sheet1!$E$2:$E$51),1) CountyList: =OFFSET(Sheet1!$F$2,0,0,100-COUNTBLANK(Sheet1!$F$2:$F$101),1) Using Data Validation for the drop down box for your state Data|Validation, Allow: List, Source: =StateList The drop down box for your count Data|Validation, Allow: List, Source: =CountyList -- John C "Chris" wrote: John, In column A I have each state listed for each county in column b. i.e. A B TX Dallas TX Denton TX Fannin I can't seem to get the drop down box to display the state only once and i also can't get the indirect statement to work. Thank you in advance for any help you can provide. Chris "John C" wrote: Since no data layout is given, I am assuming that you have lists of counties for each state. I will also assume that the state dropdown box is the 2 letter code for the state. State drop down box (cell A1) Data|Validation, Allow: List, Source: =StateList County drop down box (cell A2) Data|Validation, Allow: List, Source: =INDIRECT("CountyList"&A1) If this isn't what you are looking for, please provide more details as to how your data is laid out. -- John C "Chris" wrote: I would like to create on dropdown box with the state and then a second drop down box display only the counties for the state that was chosen in the first dropdown box. Is that possible? Thanks, Chirs |
#8
|
|||
|
|||
Dropdown box display only data dependent on another dropdown box?
See Debra Dalgleish's site for instructions using one cell only.
http://www.contextures.on.ca/xlDataVal02.html Gord Dibben MS Excel MVP On Tue, 5 Aug 2008 06:45:01 -0700, Chris wrote: I would like to create on dropdown box with the state and then a second drop down box display only the counties for the state that was chosen in the first dropdown box. Is that possible? Thanks, Chirs |
#9
|
|||
|
|||
Dropdown box display only data dependent on another dropdown b
John,
That did it! Thank you very much for your help. My spreadsheet is working great!!! "John C" wrote: Then, in my example, I have named the cell that your drop down box for your state is as myState Click on the cell that has the drop down box for your state list. Click on the Name box to the left of the formula bar (should be displaying the current cell), and then type in myState. Or, if you prefer, assume the cell that your drop down box for states is cell B2, you could replace all references to myState to $B$2. -- John C "Chris" wrote: John, So far so good, but I get a name error message in column b and f. I know is has to do with your formlua below referencing "myState". What exactly is myState and how do I name/assign it to what I believe is the range you are setting? "John C" wrote: Non-VBA solution.... Insert 2 columns before column A, so now your statelist is column C, and your county list is column D. A2: =IF(COUNTIF($C$2:$C2,C2)=1,MAX($A$1:A1)+1,"") B2: =IF(C2=myState,MAX($B$1:B1)+1,"") Copy A2 & B2 formulas down to bottom of data set. E2: =IF(ROW()-1MAX($A:$A),"",VLOOKUP(ROW()-1,$A$2:$C$1000,3,FALSE)) F2: =IF(ROW()-1MAX($B:$B),"",VLOOKUP(ROW()-1,$B$2:$D$1000,3,FALSE)) I copied E2 down to E51, and F2 down to F101. Not sure if you have all 50 states, or if you are including territories, not sure if any state has more than 100 counties, expand or modify the variables below as needed, and where you need the formulas in E and F above as needed. Then, in my example, I have named the cell that your drop down box for your state is as myState. I had to define two other variables, State List and County List Insert|Name|Define: StateList: =OFFSET(Sheet1!$E$2,0,0,50-COUNTBLANK(Sheet1!$E$2:$E$51),1) CountyList: =OFFSET(Sheet1!$F$2,0,0,100-COUNTBLANK(Sheet1!$F$2:$F$101),1) Using Data Validation for the drop down box for your state Data|Validation, Allow: List, Source: =StateList The drop down box for your count Data|Validation, Allow: List, Source: =CountyList -- John C "Chris" wrote: John, In column A I have each state listed for each county in column b. i.e. A B TX Dallas TX Denton TX Fannin I can't seem to get the drop down box to display the state only once and i also can't get the indirect statement to work. Thank you in advance for any help you can provide. Chris "John C" wrote: Since no data layout is given, I am assuming that you have lists of counties for each state. I will also assume that the state dropdown box is the 2 letter code for the state. State drop down box (cell A1) Data|Validation, Allow: List, Source: =StateList County drop down box (cell A2) Data|Validation, Allow: List, Source: =INDIRECT("CountyList"&A1) If this isn't what you are looking for, please provide more details as to how your data is laid out. -- John C "Chris" wrote: I would like to create on dropdown box with the state and then a second drop down box display only the counties for the state that was chosen in the first dropdown box. Is that possible? Thanks, Chirs |
Thread Tools | |
Display Modes | |
|
|