Results 1 to 7 of 7

Thread: Formula Help

  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    Ventura, California, USA
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Help

    I am using Excel 2k and need to have an If Then statement that uses more than one variable. If(A6="Ron",A7*45,""), If(A6="Pam",A7*50,""). I need to do this for 6 different consultants. For the life of me I cannot remember how to nest the IF THEN formula.

    Thanks for your help,

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Formula Help

    It sounds like you are using billing rates for six different consultants. Although you could nest IF statements six deep, you will be better off using a lookup table: it is much easier to maintain, and easier for a user to follow what is going on.

    Post again if you need a hand on using the table, but in short form, you would insert data like this:
    <pre> A B
    1 Mike 65
    2 Sue 60
    3 Dave 75
    (etc)
    </pre>


    In the location where you needed the rate, you would use a formula like:
    =vlookup(CellRef,$A$1:$B$3,2,0), where 'CellRef' contains the name of the consultant whose rate you want to apply.

    Hope that helps

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help

    The syntax for nested ifs is:
    =IF(A1="Ron",45,IF(A1="Pam",50,"Name Not Found")).

    Another approach is to select the list of names and run macro like:

    Sub aa()
    Set selrange = Selection
    For Each Cel In selrange

    Select Case ActiveCell

    Case "Ron"
    ActiveCell.offset(0, 1).Range("A1").Select
    ActiveCell = "45"
    Case "Pam"
    ActiveCell.offset(0, 1).Range("A1").Select
    ActiveCell = "50"
    Case Else
    ActiveCell.offset(0, 1).Range("A1").Select
    ActiveCell = "Name not found""

    End Select
    ActiveCell.offset(1, -1).Range("A1").Select
    Next Cel
    End Sub

    This puts each person's rate in the cell next to that containing their name.

  4. #4
    Star Lounger
    Join Date
    Mar 2002
    Location
    Ventura, California, USA
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help

    Thank you very much for your help. I am trying to get into the VBA side of Excel as to maximize its use. Can anyone point me to a good training web site with examples or a good book so I do not bother you folks with a lot of basic questions??

    Thanks for your help.

    Dan

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help

    See <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=xl&Number=40830&page=&v iew=&sb=&vc=1#Post40830>THIS LINK</A>

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formula Help

    You might try the following websites to get a flavour of what is involved in learning VBA. or <A target="_blank" HREF=http://www.appdev.com/newstuff/default.asp?NewPage=vbastuff.htm>AppDev

    Andrew C

  7. #7
    zenood
    Guest

    Re: Formula Help

    An excellent book on Excel programming is

    Excel VBA Programming for Dummies by John Walkenbach
    His web-site is <A target="_blank" HREF=http://www.j-walk.com>http://www.j-walk.com</A>

    I learnt all my basic VBA programming skills from this book and then went onto the more advanced stuff.

    Ciao

Posting Permissions

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