Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Mutiple IF conditions

Posted by Oli on December 20, 2001 10:26 AM
I need help with my I.T coursework!

How can i have multiple "IF" conditions?

This is the formula i have so far:
=IF(C4=1,'C:\oli\I.T\Wages\[employees.xls]Sheet1'!$C$4,"")
However i also want to include the formula:
=IF(C4=2,'C:\oli\I.T\Wages\[employees.xls]Sheet1'!$C$5,"")
and many more If conditions in the same cell, ideally it would be best if i could create some kind of a relationship between cell "C4" and what is displayed in cell C3, there is a relationship of +3 between what cell C4 equals and what i want to display in cell C3.

If there is no way to make a formula to do this i could still put in loads of IF conditions however i do not know how to do more than 1 in the same cell :(


Check out our Excel Resources

Re: Mutiple IF conditions

Posted by Joe Was on December 20, 2001 11:09 AM
You can only nest seven levels of IF statments, but you can reference a cell that includes additional IF statments to extend this limit.

The IF statement has the syntax: Result=IF(test,Then,Else)
So, with this in mind you can replace the "Else" part with a new "IF" up to seven times. You can also mix functions within your IF statements, like:

=IF(AND(I5>0,I5<5),1,IF(AND(I5>4,I5<9),2,IF(AND(I5>8,I5<13),3,IF(AND(I5>12,I5<17),4,IF(AND(I5>16,I5<21),5,IF(AND(I5>20,I5<25),6,0))))))

Note: AND(I5>0,I5<5) is: test1.
,1, is: Then 1.
IF(And(I5>4,I5<9) is: test2.
,2, is: Then 2.
...
If(AND(I5>20,I5<25) is test6.
,6, is: Then 6. and
,0)))))) is: the last Else.

This groups up to 24 collections into groups of no more than 4. With nested IF statements the trick is the "()" parentheses order. JSW


Re: Mutiple IF conditions

Posted by Tom Dickinson on December 20, 2001 11:14 AM

If its not cheating to give assitance:

Multiple (or nesting) if/then is accomplished by putting the 2nd if/then in the "else" part of the 1st if/then. Example:

If1(Condition1,Then1,If2(Condition2,Then2,Else2))
If(A=1,"Yes",If(B=1,"No","Maybe"))

For your quandry, rather than nesting, just concatenate:
='C:\oli\I.T\Wages\[employees.xls]Sheet1'!C" & C4 + 3)


Re: Mutiple IF conditions, Tom that formula dont work plz help!!

Posted by Oli on December 20, 2001 11:50 AM
the formula:
='C:\oli\I.T\Wages\[employees.xls]Sheet1'!C" & C4 + 3)

that you gave me doesnt work!

i used it in the context:
='IF(C4=1,C:\oli\I.T\Wages\[employees.xls]Sheet1'!C" & C4 + 3,"")

Is this the correct way to do it?
I am not sure you have the correct syntax here as i think excel is trying to add 3 to the value of cell c4 in the other sheet, whereas infact i want to add +3 to the cell reference!


Re: Mutiple IF conditions, Tom that formula dont work plz help!!

Posted by Juan Pablo G. on December 20, 2001 1:36 PM
Is this what you want ?

=INDIRECT("'C:\oli\I.T\Wages\[employees.xls]Sheet1'!C" & ROW() + 3)

Juan Pablo G.


Re: Mutiple IF conditions

Posted by Tom. Dickinson on December 20, 2001 7:28 PM
Ok. 2 methods.
The one from Juan, only change "row()" to "C4" os it looks like this:

=INDIRECT("'C:\oli\I.T\Wages\[employees.xls]Sheet1'!C" & C4 + 3)

The other metod is
=OFFSET("'C:\oli\I.T\Wages\[employees.xls]Sheet1'!C3", C4, 0)

Sorry for misleading you earlier, that was the programing method.


Re: Mutiple IF conditions, Tom that formula dont work plz help!!

Posted by Oli on December 21, 2001 7:24 AM
No, that aint it, i want it to be cell C4 (a numerical ID number)+ 3 = the row number of the data i want!
This data is then displayed in cell C5
see my original post also.


Re: Mutiple IF conditions

Posted by Oli on December 21, 2001 7:28 AM
THANK YOU TOM!!!

that bit o' code works great!
Thanks a lot for the help you really saved my monitor from a good punching (i was getting a "little" annoyed ;-)


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.