Validation lists...

[NWO]

Hi.

I have a list of Countries, and for each COuntry, I have some items. There
are many entires for each Country. What I'm trying to do is have 2 drop down
boxes - one for the Country, and then on for the items listed for that
country. I merely want to populate the COuntry cell with the name of the
country from the COuntry drop down, and populate the item cell with an item
selected for that selected country. Now I've tried the solution shown in the
Excel Hacks book, but the itesm part of the solution continues to give me an
error in the folowing formula:

=OFFSET(INDIRECT(ADDRESS(MATCH(Val1Cell,Country,0)+1,2,,,"Lists")),0,0,COUNTIF(Country,Val1Cell),1)

Any assistance is appreciated.

NWO.

 

[Dave Peterson]

I'd start with Debra Dalgleish's site:
http://contextures.com/xlDataVal02.html

 

[NWO]

Thnaks. Example is still too confusing and does not work. I have one lkist
- countries, and another list, items for each country. I want a drop down to
first select COuntry, and then a second drop down to chocse form a list for
that country only.

NWO

 

[Dave Peterson]

The example has worked for hundreds (probably 1000's) of people.

What part are you having trouble with?

 

[T. Valko]

=OFFSET(INDIRECT(ADDRESS(MATCH(Val1Cell,Country,0)+1,2,,,"Lists")),0,0,COUNTIF(Country,Val1Cell),1)

See if this helps:

...........A............B
1.....US..........US1
2.....US..........US2
3.....Canada...Can1
4.....Canada...Can2
5.....Mexico...Mex1
6.....Mexico...Mex2
7.....Mexico...Mex3

X1 = drop down of countries
Y1 = drop down of items for the specific country

As the source for the drop down in Y1:

=OFFSET(B1,MATCH(X1,A1:A10,0)-1,,COUNTIF(A1:A10,X1))