I have a very long employee list in column A with each individual listed by
country and would like to list the countries by region. I currently use the
AutoFilter command to select a country and then populate another column with
the region by dragging the information down. Is there a way to automate this
so if column A = Country1 then populate column b with Region1?

Example:

Column A Column B
United States North America
United States North America
Canada North America
Canada North America
Canada North America
Spain Europe
France Europe
China Asia
Chile Latin America

Thanks!

Craig

Hi Craig. Try this site. It looks like it will do what you need:
http://www.xldynamic.com/source/xld.Dropdowns.html
--
Sincerely, Michael Colvin


"Craig" wrote:

I have a very long employee list in column A with each individual listed by
country and would like to list the countries by region. I currently use the
AutoFilter command to select a country and then populate another column with
the region by dragging the information down. Is there a way to automate this
so if column A = Country1 then populate column b with Region1?

Example:

Column A Column B
United States North America
United States North America
Canada North America
Canada North America
Canada North America
Spain Europe
France Europe
China Asia
Chile Latin America

Thanks!

Craig

If you had a list of all the countries in a column and the corresponding
region in the adgacent column then you could use something like:

=IF(A1="","",VLOOKUP(A1,your_list_range,2,0))

HTH
JG

"Craig" wrote:

I have a very long employee list in column A with each individual listed by
country and would like to list the countries by region. I currently use the
AutoFilter command to select a country and then populate another column with
the region by dragging the information down. Is there a way to automate this
so if column A = Country1 then populate column b with Region1?

Example:

Column A Column B
United States North America
United States North America
Canada North America
Canada North America
Canada North America
Spain Europe
France Europe
China Asia
Chile Latin America

Thanks!

Craig

Yes, put a formula in Column B using VLOOKUP.

First, create your source matrix (you can add a worksheet for this) where
countries are listed in Column A and Regions are listed in Column B.
Ideally, name the range (see http://www.contextures.com/xlNames01.html).

On your employee page, use the following formula in Column B:

=VLOOKUP(A1,Country_List,2,false)

This formula will return #N/A if the country in column A does not exist in
Country_List.

"Craig" wrote:

I have a very long employee list in column A with each individual listed by
country and would like to list the countries by region. I currently use the
AutoFilter command to select a country and then populate another column with
the region by dragging the information down. Is there a way to automate this
so if column A = Country1 then populate column b with Region1?

Example:

Column A Column B
United States North America
United States North America
Canada North America
Canada North America
Canada North America
Spain Europe
France Europe
China Asia
Chile Latin America

Thanks!

Craig