Formula Help

Topic

New Reply

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,

Reply | Quote

Replies

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:

     A        B
1  Mike      65
2  Sue       60
3  Dave      75
(etc)

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

See THIS LINK

Reply | Quote

You might try the following websites to get a flavour of what is involved in learning VBA. or AppDev

Andrew C

Reply | Quote