Back to Forms in Excel VBA archive index

Back to archive home

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 | ||||

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

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)

the formula:

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

='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!

Is this what you want ?

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

Juan Pablo G.

Ok. 2 methods.

The one from Juan, only change "row()" to "C4" os it looks like this:

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.

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.

This data is then displayed in cell C5

see my original post also.

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.

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.