Problem with "nesting" formulas
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Problem with "nesting" formulas

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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


  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Louisville, KY
    Posts
    72
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sounds like a job for VLOOKUP


  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      

    Hello,


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


    Best regards
    Frits Jager

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com