Problem with "nesting" formulas

Flash2002 · Mar 20th, 2002, 09:03 AM

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 · Mar 20th, 2002, 09:23 AM

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 · Mar 20th, 2002, 09:24 AM

Sounds like a job for VLOOKUP

Flash2002 · Mar 21st, 2002, 12:33 PM

Hello,

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

Best regards
Frits Jager

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 20th, 2002, 09:03 AM	#1
Flash2002 New Member Join Date: Mar 2002 Posts: 4	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

Mar 20th, 2002, 09:23 AM	#2
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	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

Mar 20th, 2002, 09:24 AM	#3
Don C Board Regular Join Date: Feb 2002 Location: Louisville, KY Posts: 72	Sounds like a job for VLOOKUP

Mar 21st, 2002, 12:33 PM	#4
Flash2002 New Member Join Date: Mar 2002 Posts: 4	Hello, Thank you very much for your help. I've tried it, it works perfectly !! Best regards Frits Jager