Combining IF functions

[Scott82]

Hi guys, my brain hurts!

I am trying to combine these two statements that both work fine on their own!
=IF(ISBLANK('[Schedules Flexible worker.xls]Schedules'!$E$4:$E$12),"Call")
&
=IF('Availability IN'!D4="x","Call")

So that if both are true then "call"

I'm sure this is fairly easy but I've lost the will to live!

Many thanks

Scott

 

[JackDanIce]

Try:

=IF(AND(ISBLANK('[Schedules Flexible worker.xls]Schedules'!$E$4:$E$12),'Availability IN'!D4="x"),"Call","")

 

[morleyuk]

Try:

=IF(AND(ISBLANK('[Schedules Flexible worker.xls]Schedules'!$E$4:$E$12),'Availability IN'!D4="x"),"Call","")

 

[Scott82]

Thanks guys, it's still not working.

I've just realised that the cells E4:E12 that I am refering to have formulas in of their own (showing blank untill criteria is met.) I'm guessing "ISBLANK" refers to completly empty cells??

Thanks Scott

 

[JackDanIce]

ISBLANK won't work if the cell contains "" which appears as blank.
Try changing below to:

=IF(AND(LEN('[Schedules Flexible worker.xls]Schedules'!$E$4:$E$12)<1,'Availability IN'!D4="x"),"Call","")

 

[Scott82]

No that doesn't work I'm afraid but thank you for trying!
Do you have any other ideas?

 

[morleyuk]

Scott

What formula is in the cells you're referencing?

 

[Scott82]

Hi there, they are a variation of the one below!

<TABLE style="WIDTH: 52pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=69 x:str><COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; WIDTH: 52pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20 width=69 x:str=""> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20>=IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Giles House\2010 - 2011\[Giles House Term 6 2010-2011.xls]Sheet1'!$B$3:$B$13,0)),"","Giles")</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20 x:str=""> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20 x:str=""> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20 x:str=""> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20 x:str=""> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20 x:str=""> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: silver; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=20 x:str=""> </TD></TR></TBODY></TABLE>

Many thanks

Scott

 

[Jonmo1]

Hi guys, my brain hurts!

I am trying to combine these two statements that both work fine on their own!
=IF(ISBLANK('[Schedules Flexible worker.xls]Schedules'!$E$4:$E$12),"Call")
&
=IF('Availability IN'!D4="x","Call")

So that if both are true then "call"

I'm sure this is fairly easy but I've lost the will to live!

Many thanks

Scott

Are you sure that formula works fine on it's own?

ISBLANK can't test multiple cells for blank...it can only check a single cell.

Are you wanting to check that ALL cells in the range E4:E12 are blank, AND D4 = x, then return "Call"??

Try
=IF(AND(COUNTIF('[Schedules Flexible worker.xls]Schedules'!$E$4:$E$12,"")=9,'Availability IN'!D4="x"),"Call")

 

[morleyuk]

Scott

The ISBLANK won't work on a range. Try this:

Try:

=IF(AND(COUNTBLANK('[Schedules Flexible worker.xls]Schedules'!$E$4:$E$12)>8,'Availability IN'!D4="x"),"Call","")

This will fire if all the cells in E4:E12 are blank and D4=x.

If you want it to fire on only 1 blank then change the >8 to >0