How 2 avoid multiple IF's by LOOKUP's???

[Guest]

Hello every1,

The actual situation is quite a detailed and complex one but lets start with
a simple example:

The "sheet 1" is to be filled up with several columns like:
A B C D E
1 Case Limit Maturity-Date Balance Error
2 abc 100 12/12/2001 80 Maturity date has passed
3 def 3000 12/12/2009 4000 Account Balance is
exceeding the limit
4 ghi 5000 12/12/2008 -200 Amount is negative

"Sheet 2" has a list like:
A B
1 Error Code Message
2 1 Account Balance is exceeding the limit
3 2 Maturity date has passed
4 3 Amount is negative

I want the column E of Sheet 1 to search out the error code automatically
from Sheet 2 and return the message present in the corresponding cell in
column B.

Actually there are several multiple error checks that I want to set out but
typing out a lengthy formula of IF's is nearly impossible and risky.
Conditional formating shall also only highlight the cell but not displaying
the message and also involves the same problem of lengthy formula.

Any way out, PREFERABLY a lookup sort of formula for its applicability upon
all the the computer without lowering the macro security level, otherwise
even a macro shall be gratefully accepted! All your advices shall be valuable.

Thanx in advance,

FARAZ!

 

[Bob Phillips]

Don't see how you could avoid it.

You could two step it with a helper cell say

=IF(C2<TODAY(),1,IF(D2>B2,2,IF(D2<0,3,0)))

and then lookup the message with

=IF(E2>0,INDEX*Sheet2!B:B,MATCH(E2,Sheet2!A:A,0))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanx Bob,

I am sorry but there seemed to be some problem with my net and probably the
page was not refreshing. I thought that my question had not be posted due
some problem. Thus, I had the question replaced.

By the way can I create a MACRO as well, creating some sort of a custom
function or some other way around, so that the risk of any erroneous formula
in even one of the cell in column E could be avoided due to copying down a
lengthy one in all?

Once again I am sorry and beg your pardon.

Faraz!

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A"
Const Err1 As String = "Maturity date has passed"
Const Err2 As String = "Account Balance is exceeding the limit"
Const Err3 As String = "Amount is negative"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "E").Value = ""
If Me.Cells(.Row, "D") < 0 Then _
Me.Cells(.Row, "E").Value = Err3
If Me.Cells(.Row, "B") < Me.Cells(.Row, "D") Then _
Me.Cells(.Row, "E").Value = Err2
If Me.Cells(.Row, "C").Value < Date Then _
Me.Cells(.Row, "E").Value = Err1
'etc
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)