I am writing a complex formula and can use some help. I have two salary types in one column, and five levels of years of service in another column. I want Excel to print a different message for each of these 10 possibilities. The formula I am starting with is:
=IF(E5<=3&L5<5,"2 weeks")
where column E shows the two salary types (below 3 or above 3) and column L shows years of service.

I can get this much of the formula to work, but how can I get it to continue to print a different message for my other 8 options? Thanks.

One way would be to build a table with all the required results then
use =vlookup,Table,E5,L5) to extract the required info

DavidP

On Mon, 10 Nov 2003 08:36:04 -0800, "Kris"
wrote:

I am writing a complex formula and can use some help. I have two salary types in one column, and five levels of years of service in another column. I want Excel to print a different message for each of these 10 possibilities. The formula I am starting with is:
=IF(E5<=3&L5<5,"2 weeks")
where column E shows the two salary types (below 3 or above 3) and column L shows years of service.

I can get this much of the formula to work, but how can I get it to continue to print a different message for my other 8 options? Thanks.

If you want to do this with code, try the following ( adjusting th
columns as needed):

I set this up as follows
A2:A100 is named SalaryType
B2:B100 contains years of service
C2:C100 will contain the "Results" ie the text you want
X2:X11 is named "Text" and contains the 10 possible "answers"
such as "Two weeks" or "5 Days", "6 Days"......

There will be 2 salary types: 0 or 5
There will be 5 longevity categories: 1-5
By adding the two we get 1-10

The INDEX function in the last code line looks up the nth (1-10) valu
in the "Text" range and returns the value to column C
(which is offset 2 columns from the Salary col--adjust as needed)

The VOID line allows for rows in your database that may be emplty


Sub VacationTime()

Dim oCell As Range
Dim x As Integer
Dim y As Integer

For Each oCell In Range("SalaryType")
'Assign Salarytype category
Select Case oCell.Value
Case 1 To 3
x = 0
Case Is 3
x = 5
Case Else
GoTo VOID
End Select
'Assign longevity category
Select Case oCell.Offset(0, 1).Value 'change "1" to match th
column offset to Years col
Case 0 To 4
y = 1
Case 5 To 9
y = 2
Case 10 To 14
y = 3
Case 15 To 19
y = 4
Case Is 19
y = 5
End Select

oCell.Offset(0, 2) = Application.Index(Range("Text"), x + y)
'change the "2" to an empty column offset fro
SalaryType
VOID:
Next
End Su

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

Allow me to clarify the SalaryType column in my above post:


In my solution I assumed from your first post that this column will
contain intergers- unknown at this point- but you wanted to assign one
of two categories to each of these entries.

In my code, each entry will be assigned a value of zero if it's 1 to 3
or a value of 5 if the entry is above 3.

HTH
Paul


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/