thanks for the reference. i could not find 100% what I wanted, but another
article on contextures.com lead me to the solution of my problem, which I
herewith post for others to consider:

1. validate* column A based on the list of cities in sheet 1
2. type the following function into column B (the list of countries in which
the cities
of column A are):

=IF(A1="","",LOOKUP(A1,Sheet1!$A:$A,Sheet1!$B:$B))

3. copy cell B1
4. highlight column B and press enter

each row in column B has now the lookup function as default value,
regardless of how many rows of data the list eventually may have.

* in order for the lookup function to work properly the list of cities must
be in ascending order !!!
** in order to avoid, difficult cell references in the lookup function, give
the fields in your list names (i.e. highlight the list of cities in column A
of sheet1 and type 'city' in the name box above column A and press enter: A1
should change to city; do the same with column B and name it country); the
lookup function can then be simplified as follows:

=IF(A1="","",LOOKUP(A1,city,country))

Good Luck !

"wdjsxj" wrote:

http://www.contextures.com/excelfiles.html#Function

€œos97€�编写：

lets say i have 2 columns in sheet1: column A lists names of cities, column B
the countries they are in. in sheet2, i would like to do a data validation in
column A based on list of cities in sheet1. in addition, i would like column
B in sheet2 to automatically pick the country from the list in sheet1 in
which the selected city in column A is.

the number of rows in sheet2 could be endless and therefore copying a lookup
function from row to row is not desired.

In an older version of excel, I seem to remember a procedure using "cell
span" ?