PDA

View Full Version : How to write a formula with a bunch of 'IF' statements



nathan2314
07-15-2008, 10:10 AM
Hi all,:hi:
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...
=if(AB1="ALL","E6",if(AB1="E6","E5",if(AB1="E5","E4",if(AB1="E4","E3",if(AB1="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","")))))))))))

Apprectiate any help !! :bow:

CreganTur
07-15-2008, 10:53 AM
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:
Select Case AB1
Case Is = "All", "E6"
'steps if the above is true
Case Is = "E5"
'steps if the above is true
End Select

HTH

mae0429
07-15-2008, 11:12 AM
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
07-15-2008, 11:21 AM
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
07-15-2008, 11:22 AM
ActiveCell.Formula = "=VLOOKUP(AB1,{""ALL"",""E6"";""E6"",""E5"";""E5"",""E4"";""E4"",""E3"";""E3"",""E2"";""E2"",""E1"";""E1"",""O6"";""O6"",""O5"";""O5"",""O4"";""O4"",""O3""},2,FALSE)"

Bob Phillips
07-15-2008, 11:23 AM
You can only have 7 nested functions in a formula in Excel pre-2007.

Bob Phillips
07-15-2008, 11:24 AM
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
07-15-2008, 11:31 AM
Ooo thats good. I'm not sure how it works but it does the job nicely :clap:
thanks!!