Multiple IF statements help needed

Bumlak

New Member
Joined
Mar 19, 2009
Messages
4
I couldn't find the exact answer to this question on the boards, but hopefully one of the experts here can help.

I am working on an automated form for some of my trainers. Within the form, I have a drop down box with 23 different selections (this is for termination reason codes such as ATT for attendance, JOB for Job Abandonment etc.)

When one of the 23 selections is chosen, I need a field to select one of 5 different options (Termination Voluntary, Termination Involuntary, Verbal Counseling, Written Counseling or Final Warning.)

The issue I am running into is the rule that you can only have 7 IF statements in the formula. I don't want to create multiple fields if there is another way around it.

Could anyone lend assistance?

Thanks,

Erich
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi and welcome to the Board
Use Code. A short example! Say your dropdown is in B3
Code:
Private Sub WorkSheet_Change(ByVal Target as Range)
If Target.Address <> "$B$3" Then Exit Sub
Select Case UCase(Target)
      Case "ATT","XXX","FFF":Cells(3,"C") = "Termination Involuntary"
      Case "DDD","RRR","ZZZ":Cells(3,"C") = "Termination Voluntary"
      'etc
      Case Else:
End Select
End Sub
This would place the comment in "C3". Adjust as needed
lenze
 
Upvote 0
what about listing whichever of the 5 options each of the 23 selections applies to in the column next to it (i'm assuming you're using a list in a sheet to populate your combo.)

Then you can use vlookup to pull out this second value,using the first value as a reference.
 
Upvote 0
Maybe a lookup table would be better for this?

Excel Workbook
AB
1selection1option1
2selection2option2
3selection3option3
4selection4option4
5selection5option5
6selection6option1
7selection7option2
8selection8option3
9selection9option4
10selection10option5
11selection11option1
12selection12option2
13selection13option3
14selection14option4
15selection15option5
16selection16option1
17selection17option2
18selection18option3
19selection19option4
20selection20option5
21selection21option1
22selection22option2
23selection23option3
24
25
26
27
28
29selection6option1
Sheet1
 
Upvote 0
Thanks to all who responded. I used the VLOOKUP option and it worked fine for my porposes.

Again..many thanks,

Erich
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,916
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top