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.

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

NWO wrote:

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

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" wrote:

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

NWO wrote:

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

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

What part are you having trouble with?

NWO wrote:

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" wrote:

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

NWO wrote:

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


--

Dave Peterson

=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))


--
Biff
Microsoft Excel MVP


"NWO" wrote in message
...
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" wrote:

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

NWO wrote:

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