Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How 2 avoid multiple IF's by LOOKUP's???
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How 2 avoid multiple IF's by LOOKUP's???
Don't see how you could avoid it.
You could two step it with a helper cell say =IF(C2<TODAY(),1,IF(D2B2,2,IF(D2<0,3,0))) and then lookup the message with =IF(E20,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) "FARAZ QURESHI" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How 2 avoid multiple IF's by LOOKUP's???
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" wrote: Don't see how you could avoid it. You could two step it with a helper cell say =IF(C2<TODAY(),1,IF(D2B2,2,IF(D2<0,3,0))) and then lookup the message with =IF(E20,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) "FARAZ QURESHI" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How 2 avoid multiple IF's by LOOKUP's???
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:D" 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) "FARAZ QURESHI" wrote in message ... 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" wrote: Don't see how you could avoid it. You could two step it with a helper cell say =IF(C2<TODAY(),1,IF(D2B2,2,IF(D2<0,3,0))) and then lookup the message with =IF(E20,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) "FARAZ QURESHI" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple IF's to Select a Value | Excel Worksheet Functions | |||
Multiple If's and OR's | Excel Worksheet Functions | |||
multiple IF's ? | Excel Worksheet Functions | |||
Formula with multiple IF'S | Excel Discussion (Misc queries) | |||
Lookup's for and totals | Excel Worksheet Functions |