I have a cell in my workbook that I would like to have filled from a
dropdown list. The possible option number about 30 so a "Data Validation"
would be unwieldly unless I could nest it, as the possibilities fall into 3
to 4 catagories. Is there a way to do this? I thought about a combo box,
but I want the same dropdown to be usable by approx. 120 cells. Is there a
macro that could be done in wich I select the cell and then a pop-up window
will allow the fill, or some way around this?

I am using Excel 97.

I think this might be a good place to start:
http://www.contextures.com/xlDataVal02.html
Check dependent dropdown lists

Does that help?

***********
Regards,
Ron


"Adam Kroger @hotmail.com" wrote:

I have a cell in my workbook that I would like to have filled from a
dropdown list. The possible option number about 30 so a "Data Validation"
would be unwieldly unless I could nest it, as the possibilities fall into 3
to 4 catagories. Is there a way to do this? I thought about a combo box,
but I want the same dropdown to be usable by approx. 120 cells. Is there a
macro that could be done in wich I select the cell and then a pop-up window
will allow the fill, or some way around this?

I am using Excel 97.

A further elaboration of the type of data I want to get into the box,
because I realized I didn't explain it very well. The following example
isn't what I am trying to do, but it will draw the correct picture. The
actual entries I want would be the name of cities. the nestings would be
like State - County - City

I have other data that will autofill into cells along the same row as the
fill, with data related to the selection, that is already contained in a
table on another sheet.


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
I have a cell in my workbook that I would like to have filled from a
dropdown list. The possible option number about 30 so a "Data Validation"
would be unwieldly unless I could nest it, as the possibilities fall into 3
to 4 catagories. Is there a way to do this? I thought about a combo box,
but I want the same dropdown to be usable by approx. 120 cells. Is there a
macro that could be done in wich I select the cell and then a pop-up window
will allow the fill, or some way around this?

I am using Excel 97.

This may not be exactly what you want, but I'm hoping it's something you can
work with.
Let's use a test model:

Start with a new workbook containing Sheet1 and Sheet2

On Sheet1, set up the following:
A1: LU_StateList
B1: CntyStart
C1: CntyEnd

A2: State1
A3: State2

F1: LU_CountyList
G1: LU_StCntyList
H1: CityStart
I1: CityEnd

E2: State1
E3: State1
E4: State2
E5: State2

F2: County1
F3: County2
F4: County3
F5: County4

L1: LU_CityList
K2: State1County1
K3: State1County1
K4: State1County1
K5: State1County1
K6: State1County2
K7: State1County2
K8: State1County2
K9: State2County2
K10: State2County3
K11: State2County3
K12: State2County3
K13: State2County3
K14: State2County4
K15: State2County4
K16: State2County4
K17: State2County4

L2: City1
L3: City2
L4: City3
L5: City4
L6: City5
L7: City6
L8: City7
L9: City8
L10: City9
L11: City10
L12: City11
L13: City12
L14: City13
L15: City14
L16: City15
L17: City16

B2: =MATCH(A2,$E$2:$E$5,0)-1
Copy that to B3

C2: =COUNTIF($E$2:$E$5,A2)
Copy that to C3

G2: =E2&F2
Copy that to G5

H2: =MATCH(E2&F2,$K$2:$K$17,0)-1
Copy that to H5

I2: =COUNTIF($K$2:$K$17,E2&F2)
Copy that to I5

Now, we need some range names:
InsertNameDefine
RangeName Reference
LU_CityList =Sheet1!$L$2:$L$17
LU_CountyList =Sheet1!$F$2:$I$5
LU_StateList =Sheet1!$A$2:$C$3
LU_StCntyList =Sheet1!$G$2:$I$5

Now for Sheet2...the data validations:
A1: State
B1: County
C1: City

A2: =OFFSET(LU_StateList,0,0,,1)
Copy that cell down a few cells

B2:
=OFFSET(LU_CountyList,VLOOKUP(A2,LU_StateList,2,0) ,,VLOOKUP(A2,LU_StateList,3,0),1)
Copy that cell down a few cells

C2:
=OFFSET(LU_CityList,VLOOKUP(A2&B2,LU_StCntyList,2, 0),,VLOOKUP(A2&B2,LU_StCntyList,3,0),1)
Copy that cell down a few cells

Next...Testing
Click A2 and select a state
Click B2 and select a county (should correspond to the state)
Click C2 and select a city (should correspond to the county/state)

Try again for row3..using a different state.

Something you can use?
***********
Regards,
Ron


"Adam Kroger @hotmail.com" wrote:

A further elaboration of the type of data I want to get into the box,
because I realized I didn't explain it very well. The following example
isn't what I am trying to do, but it will draw the correct picture. The
actual entries I want would be the name of cities. the nestings would be
like State - County - City

I have other data that will autofill into cells along the same row as the
fill, with data related to the selection, that is already contained in a
table on another sheet.


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
I have a cell in my workbook that I would like to have filled from a
dropdown list. The possible option number about 30 so a "Data Validation"
would be unwieldly unless I could nest it, as the possibilities fall into 3
to 4 catagories. Is there a way to do this? I thought about a combo box,
but I want the same dropdown to be usable by approx. 120 cells. Is there a
macro that could be done in wich I select the cell and then a pop-up window
will allow the fill, or some way around this?

I am using Excel 97.