Data Validation/Fill question

[Adam Kroger]

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.

[Ron Coderre]

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.
>
>
>
>

[Adam Kroger]

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
news:[email protected]...
>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.
>
>
>

[Ron Coderre]

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:
Insert>Name>Define
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
> news:[email protected]...
> >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.
> >
> >
> >
>
>
>