How to write a formula with a bunch of 'IF' statements

[nathan2314]

Hi all,
I'm trying to write a formula in a cell that is pretty straight forward. It just has a bunch of IF statements. The problem is that when I get to a certain number of IF statements the formula stops working. I'm guessing there is a limit to the number of IF statements allowed? Is there another way of doing it? What I'm trying to do is set the value of the cell based off of a value selected by a user in a drop down combo box. When the use makes his/her selection, this cell will be shifted on value off in the list. code below...
[VBA]=if(AB1="ALL","E6",if(AB1="E6","E5",if(AB1="E5","E4",if(AB1="E4","E3",if(AB 1="E3","E2",if(AB1="E2","E1",if(AB1="E1","O6",if(AB1="O6","O5",if(AB1="O5", "O4",if(AB1="O4","O3",if(AB1="O3","O2","")))))))))))[/VBA]

Apprectiate any help !!

[CreganTur]

Using VBA to handle your formula would be better than trying to write the formula into a cell. Also, you should use Select Case in this instance, instead of a mass of If statements.

Here's a rough example:
[vba]Select Case AB1
Case Is = "All", "E6"
'steps if the above is true
Case Is = "E5"
'steps if the above is true
End Select[/vba]

HTH

[mae0429]

I agree that Select Case is great, but one thing, Randy: I'm assuming that Nathan wants this to be interactive (make a selection and changes are reflected on the sheet). If you use VBA, won't he have to re-run the macro each time to update? (j/w)

Also, Nathan, what do you mean by this (I don't quite understand):

When the use makes his/her selection, this cell will be shifted on value off in the list.

-Matt

[nathan2314]

Hi all,
I guess I could use VBA. I would have to run the macro each time a user changed his/her selection from the combo list.
Matt:
Sorry was typing abit to fast. I was trying to write 'When the user makes his/her selection, this cell (the one I'm trying to calculate now with all the IF statement or VBA code) will be a value in the combo list but just shifted down the list one from what the user just selected.
Thanks guys, I'll try the VBA approach.
Does anybody know why the IF statement wont work though. It seems like after 8 if statements it stops working ??? weird ?

[Bob Phillips]

[vba]

ActiveCell.Formula = "=VLOOKUP(AB1,{""ALL"",""E6"";""E6"",""E5"";""E5"",""E4"";""E4"",""E3"";""E 3"",""E2"";""E2"",""E1"";""E1"",""O6"";""O6"",""O5"";""O5"",""O4"";""O4""," "O3""},2,FALSE)"
[/vba]

[Bob Phillips]

You can only have 7 nested functions in a formula in Excel pre-2007.

[Bob Phillips]

Sorry, I should have given you

=VLOOKUP(AB1,{"ALL","E6";"E6","E5";"E5","E4";"E4","E3";"E3","E2";"E2","E1"; "E1","O6";"O6","O5";"O5","O4";"O4","O3"},2,FALSE)

[nathan2314]

Ooo thats good. I'm not sure how it works but it does the job nicely
thanks!!