Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case selection in Excel 2003
I am working on a form that can be used for 15 different branches.'
If I type branch "10", info for branch 10 will display. If I type branch "20", info for branch 20 will display. I can do nested "if" statements, but I can only do up to 7 nested statements, not for 15. Is there a way to accomplish this? Any help is greatly appreciated. Thanks, m2 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case selection in Excel 2003
You could look at the CHOOSE() function for the worksheet, but I think the
numbers are going to be out of range for you. Typically this is done with a table and a VLOOKUP() function in a cell. You'd have a table somewhere set up like this: C D 1 10 "Branch 10 Information" 2 20 "Branch 20 Information" .... 15 150 "Branch 150 Info" Now you enter the branch you want into a cell, say A1 and somewhere you have a VLOOKUP() formula like this: =VLOOKUP($A$1,$C$1:$D$15,2,FALSE) The table can even be on another sheet, you just have to add the sheet name to the formula where it references the table's range. That will return a #N/A error when there's no match (as when A1 is empty), so you can wrap it in an 'error trap' like this: =IF(ISNA(=VLOOKUP($A$1,$C$1:$D$15,2,FALSE)),"",=VL OOKUP($A$1,$C$1:$D$15,2,FALSE)) which will either show you the value from column D when a match is found in column C to the entry in A1 or it will just look empty when there isn't a match. Hope this helps some. "m2work" wrote: I am working on a form that can be used for 15 different branches.' If I type branch "10", info for branch 10 will display. If I type branch "20", info for branch 20 will display. I can do nested "if" statements, but I can only do up to 7 nested statements, not for 15. Is there a way to accomplish this? Any help is greatly appreciated. Thanks, m2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case selection in Excel 2003
One option:
Suppose in sheet1 you have 1-15 in Col A, Branch Names in ColB, Manager's name in Col C Then to show branch name in B1 of sheet2 you can use =VLOOKUP(A1, Sheet1!A:C,2,False) It will show the branch name corresponding to the number you enter in B1 Similary in C1 you can have =VLOOKUP(A1, Sheet1!A:C,2,False) to show manager's name.... "m2work" wrote: I am working on a form that can be used for 15 different branches.' If I type branch "10", info for branch 10 will display. If I type branch "20", info for branch 20 will display. I can do nested "if" statements, but I can only do up to 7 nested statements, not for 15. Is there a way to accomplish this? Any help is greatly appreciated. Thanks, m2 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case selection in Excel 2003
One interp on your set-up
and a way via index/match Branch data is assumed in Sheet2, cols A to E, with key col (branch codes) listed in col B [data per branch is assumed on a std row-wise, single row basis] In Sheet1 (your form), you have the branch codes listed in A2 down Put in B2: =IF(ISNA(MATCH($A2,Sheet2!$B:$B,0)),"",INDEX(Sheet 2!A:A,MATCH($A2,Sheet2!$B:$B,0))) Copy B2 across by 5 cols to F2, fill down as far as required. -- Max Singapore http://savefile.com/projects/236895 Downloads:20,000 Files:362 Subscribers:62 xdemechanik --- "m2work" wrote: I am working on a form that can be used for 15 different branches.' If I type branch "10", info for branch 10 will display. If I type branch "20", info for branch 20 will display. I can do nested "if" statements, but I can only do up to 7 nested statements, not for 15. Is there a way to accomplish this? Any help is greatly appreciated. Thanks, m2 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case selection in Excel 2003
Hi,
Suppose you only had a single column of info to return, so your lookup table was in A1:B15 with the branchs listed in the A column. If you enter the branch you want to lookup in D1, than the formula would be =LOOKUP(D1,A1:B15) This requires that your table, A1:B15 be sorted in ascending order on column A. If you want to return data in column C based on a match in column A then =LOOKUP(D1,A1:A15,C1:C15) or shorter still: =LOOKUP(D1,A1:C15) -- Thanks, Shane Devenshire "m2work" wrote: I am working on a form that can be used for 15 different branches.' If I type branch "10", info for branch 10 will display. If I type branch "20", info for branch 20 will display. I can do nested "if" statements, but I can only do up to 7 nested statements, not for 15. Is there a way to accomplish this? Any help is greatly appreciated. Thanks, m2 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Case selection in Excel 2003
Thanks, it works.
Also, thanks everyone for providing some great insight! Thanks, m2 "Sheeloo" wrote: One option: Suppose in sheet1 you have 1-15 in Col A, Branch Names in ColB, Manager's name in Col C Then to show branch name in B1 of sheet2 you can use =VLOOKUP(A1, Sheet1!A:C,2,False) It will show the branch name corresponding to the number you enter in B1 Similary in C1 you can have =VLOOKUP(A1, Sheet1!A:C,2,False) to show manager's name.... "m2work" wrote: I am working on a form that can be used for 15 different branches.' If I type branch "10", info for branch 10 will display. If I type branch "20", info for branch 20 will display. I can do nested "if" statements, but I can only do up to 7 nested statements, not for 15. Is there a way to accomplish this? Any help is greatly appreciated. Thanks, m2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Case Statements in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2003 can't do case sensitive sorting! | Excel Discussion (Misc queries) | |||
how do i change a case to proper text in excel 2003 | Excel Worksheet Functions | |||
Copying Excel 2003 Selection into Outlook 2003 HTML E-Mail Message | Excel Discussion (Misc queries) | |||
does anyone have the excel 2003 tutorial 3 case 4 (ortitiz marine. | Excel Discussion (Misc queries) |