I have to relate a list of Names to a special code, no problem with up to 7
by using the nest function.
example :
A B C D
10 USA ABC Co. 93246
20 Canada DEF Firm 38490
30 Brasil XXY Ltd. 12345
40 Germany ... ... ...
50 Egypt ...
....
For the moment I work with =IF(B2="USA";10;IF(B3="Canada";20;IF(B4="Brasil ...
my list of names is longer than 7, which does not work, or ?
Intention is to fill automatically a column ( in this case A ) referred to a
column you just typed in (e.g. B ).

Why don't you use a Vlookup to do this.

Somewhere on the side create a table that lists the countries in the
first column and the associated numbers in the second.

Then in Column A, enter formula =Vlookup(B2,$AA$1:$AB$100,2,0), where
AA1:AB100 contains your lookup table, then you can copy the formula
down column A.


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489402

Vlookup would be your answer if the column from which you wanted to return
data (A in your example) were right of the column on which that value is
based (B). Since that's not the case here, you'd have to combine MATCH to
determine the correct row with INDEX to get that row's data from column A:
=index(a:a,match("USA",b:b,false))

"pirat2104" wrote:

I have to relate a list of Names to a special code, no problem with up to 7
by using the nest function.
example :
A B C D
10 USA ABC Co. 93246
20 Canada DEF Firm 38490
30 Brasil XXY Ltd. 12345
40 Germany ... ... ...
50 Egypt ...
...
For the moment I work with =IF(B2="USA";10;IF(B3="Canada";20;IF(B4="Brasil ...
my list of names is longer than 7, which does not work, or ?
Intention is to fill automatically a column ( in this case A ) referred to a
column you just typed in (e.g. B ).

It's seems to me that the intent of the OP is to fill column A with
values corresponding to those country names. The index function
proposed indexes column A and will therefore get a circular reference
error.

I could be wrong...in my interpretation though.


--
Vito
------------------------------------------------------------------------
Vito's Profile: http://www.excelforum.com/member.php...o&userid=29182
View this thread: http://www.excelforum.com/showthread...hreadid=489402