Problem with "nesting" formulas

[Flash2002]

Hello,


I'm having the following little problem.

I have entered 10 codes (1 thru 10) in the cells A1 to A10, and 10 different values in the cells B1 to B10 in an Excel worksheet.


Worksheet 1

Codes Days to wait
A1=1 B1=10
A2=2 B2=20
. .
. .
. .
. .
A10=10 B10=100


Worksheet 2

In another worksheet the user can enter a code (1 to 10) in cells A1 to A10, (for example A1=2).

The Excel sheet has to search for the "2" in Worksheet 1 (found in cell A2) and add
the corresponding value in "Days to wait" (in cell B2) to the date entered in cell B1 in Worksheet 2.

The result of this calculation must be shown in C1 in Worksheet 2.


Codes Date New Date
A1=-2-- B1=January 1st 2002 C1=----
A2=---- B2=January 2nd 2002 C2=----
. . .
. . .
. . .
. . .
A10=--- B10=January 10 2002 C3=----


I can't use the "Nesting" method with the "IF" statement, because nesting only allows 7 "IF's". (I have 10....)

Is there a way to solve this problem using formulas and functions only, or do I have to use "Visual Basic for Applications" for this ?

Please help....




Best regards,

Frits Jager

 

[IML]

You should be able to vlookup. The format is
=VLOOKUP(A1,Sheet1!$A$1:$B$10,2,0)

will return the days per the code. So then simply add this to your date
=VLOOKUP(A1,Sheet1!$A$1:$B$10,2,0)+B1

and format the date how you like.

good luck

 

[Don C]

Sounds like a job for VLOOKUP

 

[Flash2002]

Hello,


Thank you very much for your help.
I've tried it, it works perfectly !!


Best regards
Frits Jager