Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
20 level nested If
Hi ...
I have a problem, and I would be very thankful if someone can help... In my Excel worksheet I have Two columns: 'Column A' and 'Column B'. 'Column A' is for Action Codes (e.g. 1001, 1002, 1003,... 1020), while 'Column B' is for Actions (e.g. "adjust against payable", "Issue Credit Note", "Reconcile customer's account, and apply credits",... "action 20"). I have 20 'Actions' to which I need to assign 20 'Action Codes'. What I need to do is this: whenever I enter the 'Action Code' in any cell in 'column A', the corresponding 'Action' that I have assigned to that 'Action Code' will automatically appear in the adjacent cell in 'Column B'. For simplification, let's assume that these are the 'action codes' and the corresponding 'actions': Action Code Action 1001 'action 1' 1002 'action 2' 1003 'action 3' 1004 'action 4' 1005 'action 5' 1006 'action 6' 1007 'action 7' 1008 'action 8' 1009 'action 9' 1010 'action 10' 1011 'action 11' 1012 'action 12' 1013 'action 13' 1014 'action 14' 1015 'action 15' 1016 'action 16' 1017 'action 17' 1018 'action 18' 1019 'action 19' 1020 'action 20' |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
20 level nested If
Hi
You could only use 20 levels of nested If in XL2007, and even then it would be very unwieldy and difficult to maintain. Far better to use a lookup table. Create a list of your numbers in A1:A20 on sheet2, and your actions in B1:B20 of the same sheet. On sheet 1, in cell B1 enter =VLOOKUP(A1,Sheet2!$A$1:$B$20,2) -- Regards Roger Govier "Hi_no_Tori" wrote in message ... Hi ... I have a problem, and I would be very thankful if someone can help... In my Excel worksheet I have Two columns: 'Column A' and 'Column B'. 'Column A' is for Action Codes (e.g. 1001, 1002, 1003,... 1020), while 'Column B' is for Actions (e.g. "adjust against payable", "Issue Credit Note", "Reconcile customer's account, and apply credits",... "action 20"). I have 20 'Actions' to which I need to assign 20 'Action Codes'. What I need to do is this: whenever I enter the 'Action Code' in any cell in 'column A', the corresponding 'Action' that I have assigned to that 'Action Code' will automatically appear in the adjacent cell in 'Column B'. For simplification, let's assume that these are the 'action codes' and the corresponding 'actions': Action Code Action 1001 'action 1' 1002 'action 2' 1003 'action 3' 1004 'action 4' 1005 'action 5' 1006 'action 6' 1007 'action 7' 1008 'action 8' 1009 'action 9' 1010 'action 10' 1011 'action 11' 1012 'action 12' 1013 'action 13' 1014 'action 14' 1015 'action 15' 1016 'action 16' 1017 'action 17' 1018 'action 18' 1019 'action 19' 1020 'action 20' |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
20 level nested If
Instead of a nested IF, it sounds like you need a VLOOKUP. An alternative
might be to use CHOOSE. The functions are shown, with exampls, in Excel's help. -- David Biddulph "Hi_no_Tori" wrote in message ... Hi ... I have a problem, and I would be very thankful if someone can help... In my Excel worksheet I have Two columns: 'Column A' and 'Column B'. 'Column A' is for Action Codes (e.g. 1001, 1002, 1003,... 1020), while 'Column B' is for Actions (e.g. "adjust against payable", "Issue Credit Note", "Reconcile customer's account, and apply credits",... "action 20"). I have 20 'Actions' to which I need to assign 20 'Action Codes'. What I need to do is this: whenever I enter the 'Action Code' in any cell in 'column A', the corresponding 'Action' that I have assigned to that 'Action Code' will automatically appear in the adjacent cell in 'Column B'. For simplification, let's assume that these are the 'action codes' and the corresponding 'actions': Action Code Action 1001 'action 1' 1002 'action 2' 1003 'action 3' 1004 'action 4' 1005 'action 5' 1006 'action 6' 1007 'action 7' 1008 'action 8' 1009 'action 9' 1010 'action 10' 1011 'action 11' 1012 'action 12' 1013 'action 13' 1014 'action 14' 1015 'action 15' 1016 'action 16' 1017 'action 17' 1018 'action 18' 1019 'action 19' 1020 'action 20' |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
20 level nested If
Thanks for responding to my post quickly, Roger. I tried the method you
suggested, and it worked for me, but there's a slight problem. When I enter an action code that is out of the the range of Action Codes (e.g. 10020), it gives me the same result as I entered the action code 1020. Can I do something to make it more accurate? when I enter anything other than the 20 'action codes' (1001-1020) in sheet 2, I just want the cell in 'column B' to be to be left blank. "Roger Govier" wrote: Hi You could only use 20 levels of nested If in XL2007, and even then it would be very unwieldy and difficult to maintain. Far better to use a lookup table. Create a list of your numbers in A1:A20 on sheet2, and your actions in B1:B20 of the same sheet. On sheet 1, in cell B1 enter =VLOOKUP(A1,Sheet2!$A$1:$B$20,2) -- Regards Roger Govier "Hi_no_Tori" wrote in message ... Hi ... I have a problem, and I would be very thankful if someone can help... In my Excel worksheet I have Two columns: 'Column A' and 'Column B'. 'Column A' is for Action Codes (e.g. 1001, 1002, 1003,... 1020), while 'Column B' is for Actions (e.g. "adjust against payable", "Issue Credit Note", "Reconcile customer's account, and apply credits",... "action 20"). I have 20 'Actions' to which I need to assign 20 'Action Codes'. What I need to do is this: whenever I enter the 'Action Code' in any cell in 'column A', the corresponding 'Action' that I have assigned to that 'Action Code' will automatically appear in the adjacent cell in 'Column B'. For simplification, let's assume that these are the 'action codes' and the corresponding 'actions': Action Code Action 1001 'action 1' 1002 'action 2' 1003 'action 3' 1004 'action 4' 1005 'action 5' 1006 'action 6' 1007 'action 7' 1008 'action 8' 1009 'action 9' 1010 'action 10' 1011 'action 11' 1012 'action 12' 1013 'action 13' 1014 'action 14' 1015 'action 15' 1016 'action 16' 1017 'action 17' 1018 'action 18' 1019 'action 19' 1020 'action 20' |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
20 level nested If
Hi
Including the optional 4th parameter in Vlookup of False or ), would stop it from giving the last result. =VLOOKUP(A1,Sheet2!$A$1:$B$20,2,0) I had omitted the optional parameter as your list is sorted, and the Vlookup runs faster in this format (though not noticeable on a short list like this), so I should perhaps have included it. However, it would give a #N/A error if the value did not exits in the list. One way around the problem would be =IF(AND(A11000,A1<1020),=VLOOKUP(A1,Sheet2!$A$1:$ B$20,2,0),"") -- Regards Roger Govier "Hi_no_Tori" wrote in message ... Thanks for responding to my post quickly, Roger. I tried the method you suggested, and it worked for me, but there's a slight problem. When I enter an action code that is out of the the range of Action Codes (e.g. 10020), it gives me the same result as I entered the action code 1020. Can I do something to make it more accurate? when I enter anything other than the 20 'action codes' (1001-1020) in sheet 2, I just want the cell in 'column B' to be to be left blank. "Roger Govier" wrote: Hi You could only use 20 levels of nested If in XL2007, and even then it would be very unwieldy and difficult to maintain. Far better to use a lookup table. Create a list of your numbers in A1:A20 on sheet2, and your actions in B1:B20 of the same sheet. On sheet 1, in cell B1 enter =VLOOKUP(A1,Sheet2!$A$1:$B$20,2) -- Regards Roger Govier "Hi_no_Tori" wrote in message ... Hi ... I have a problem, and I would be very thankful if someone can help... In my Excel worksheet I have Two columns: 'Column A' and 'Column B'. 'Column A' is for Action Codes (e.g. 1001, 1002, 1003,... 1020), while 'Column B' is for Actions (e.g. "adjust against payable", "Issue Credit Note", "Reconcile customer's account, and apply credits",... "action 20"). I have 20 'Actions' to which I need to assign 20 'Action Codes'. What I need to do is this: whenever I enter the 'Action Code' in any cell in 'column A', the corresponding 'Action' that I have assigned to that 'Action Code' will automatically appear in the adjacent cell in 'Column B'. For simplification, let's assume that these are the 'action codes' and the corresponding 'actions': Action Code Action 1001 'action 1' 1002 'action 2' 1003 'action 3' 1004 'action 4' 1005 'action 5' 1006 'action 6' 1007 'action 7' 1008 'action 8' 1009 'action 9' 1010 'action 10' 1011 'action 11' 1012 'action 12' 1013 'action 13' 1014 'action 14' 1015 'action 15' 1016 'action 16' 1017 'action 17' 1018 'action 18' 1019 'action 19' 1020 'action 20' |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
20 level nested If
Many Thanks, Roger. I tried what you suggested, and it works. Thank you very
much for your help. "Roger Govier" wrote: Hi Including the optional 4th parameter in Vlookup of False or ), would stop it from giving the last result. =VLOOKUP(A1,Sheet2!$A$1:$B$20,2,0) I had omitted the optional parameter as your list is sorted, and the Vlookup runs faster in this format (though not noticeable on a short list like this), so I should perhaps have included it. However, it would give a #N/A error if the value did not exits in the list. One way around the problem would be =IF(AND(A11000,A1<1020),=VLOOKUP(A1,Sheet2!$A$1:$ B$20,2,0),"") -- Regards Roger Govier "Hi_no_Tori" wrote in message ... Thanks for responding to my post quickly, Roger. I tried the method you suggested, and it worked for me, but there's a slight problem. When I enter an action code that is out of the the range of Action Codes (e.g. 10020), it gives me the same result as I entered the action code 1020. Can I do something to make it more accurate? when I enter anything other than the 20 'action codes' (1001-1020) in sheet 2, I just want the cell in 'column B' to be to be left blank. "Roger Govier" wrote: Hi You could only use 20 levels of nested If in XL2007, and even then it would be very unwieldy and difficult to maintain. Far better to use a lookup table. Create a list of your numbers in A1:A20 on sheet2, and your actions in B1:B20 of the same sheet. On sheet 1, in cell B1 enter =VLOOKUP(A1,Sheet2!$A$1:$B$20,2) -- Regards Roger Govier "Hi_no_Tori" wrote in message ... Hi ... I have a problem, and I would be very thankful if someone can help... In my Excel worksheet I have Two columns: 'Column A' and 'Column B'. 'Column A' is for Action Codes (e.g. 1001, 1002, 1003,... 1020), while 'Column B' is for Actions (e.g. "adjust against payable", "Issue Credit Note", "Reconcile customer's account, and apply credits",... "action 20"). I have 20 'Actions' to which I need to assign 20 'Action Codes'. What I need to do is this: whenever I enter the 'Action Code' in any cell in 'column A', the corresponding 'Action' that I have assigned to that 'Action Code' will automatically appear in the adjacent cell in 'Column B'. For simplification, let's assume that these are the 'action codes' and the corresponding 'actions': Action Code Action 1001 'action 1' 1002 'action 2' 1003 'action 3' 1004 'action 4' 1005 'action 5' 1006 'action 6' 1007 'action 7' 1008 'action 8' 1009 'action 9' 1010 'action 10' 1011 'action 11' 1012 'action 12' 1013 'action 13' 1014 'action 14' 1015 'action 15' 1016 'action 16' 1017 'action 17' 1018 'action 18' 1019 'action 19' 1020 'action 20' |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
3 level nested if has too many arguments ? | Excel Discussion (Misc queries) | |||
3 level nested if please explain | Excel Discussion (Misc queries) | |||
nested if based on nested if in seperate sheet. how? | Excel Worksheet Functions | |||
Limited IF Nested Level functions. | Excel Discussion (Misc queries) | |||
Level of protection | Excel Discussion (Misc queries) |