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

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

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

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

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

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