1. ## 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.

2. ## 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. ## Re: Formula Help

The syntax for nested ifs is:

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

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. ## 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??

Dan

6. ## 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. ## 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
•