Expert required for new formula

[Scott82]

Hi there

I am using two formulas
=IF(AND(COUNTBLANK('S:\School\Residential\Flexible Workers\Term 6 2011\[Schedules Flexible worker Term 6 2011.xls]Schedules'!E112:E120)>8,'Availability IN'!D16="x"),"Available","")

and

=SUM(C5+C8+C11+C14+C17+C20+C23)

I need a formula that will return True if the first formula shows available and the second is equel to or above 1.

Many thanks

Scott

 

[yytsunamiyy]

That means that

'[Schedules Flexible worker Term 6 2011.xls]Schedules'!E4:E12

produces an error. There is something wrong with your range. for me to have a look at your range please do the following:

- Open the Forum and this thread in Internet Explorer (do not use another browser)
- Highlight your range ('[Schedules Flexible worker Term 6 2011.xls]Schedules'!E4:E12) in Excel
- copy the range (right-click ->copy or CTRL + C)
- paste into your answer (right-click ->paste or CTRL + V)
- submit your answer

That way we can see what your range actually contains.

 

[Scott82]

Hi there, please see 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: #ece9d8; 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: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>School</TD></TR></TBODY></TABLE>

I hope this works!

 

[Scott82]

<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: #ece9d8; 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=""> =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: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17 x:str=""> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: silver; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl24 height=17>School</TD></TR></TBODY></TABLE>

I've inserted the formula for you also!

 

[yytsunamiyy]

Looks like the range is not referenced correctly. Try replacing it with your fully qualified reference form your first post (replace '[Schedules Flexible worker Term 6 2011.xls]Schedules'!E4:E12 with 'S:\School\Residential\Flexible Workers\Term 6 2011\[Schedules Flexible worker Term 6 2011.xls]Schedules'!E4:E12)

 

[Scott82]

Argh! I did as you said but the formula is too long!

 

[yytsunamiyy]

I'll have to test later. What version of XL are you on?

 

[Scott82]

Ok, thank you. I'm on 2003.

Are you not able to refer to the result of the formula in the cell you are refering to?
ie the reuslt of =IF(AND(COUNTBLANK('S:\School\Residential\Flexible Workers\Term 6 2011\[Schedules Flexible worker Term 6 2011.xls]Schedules'!E112:E120)>8,'Availability IN'!D16="x"),"Available","") is Available so can you not say cell?? ="Available" and cell?? =>1 then True??

Thanks Scott

 

[yytsunamiyy]

Ok, thank you. I'm on 2003.

Are you not able to refer to the result of the formula in the cell you are refering to?
ie the reuslt of =IF(AND(COUNTBLANK('S:\School\Residential\Flexible Workers\Term 6 2011\[Schedules Flexible worker Term 6 2011.xls]Schedules'!E112:E120)>8,'Availability IN'!D16="x"),"Available","") is Available so can you not say cell?? ="Available" and cell?? =>1 then True??

Thanks Scott

Of course you can. Simply use

=IF(AND(Cell1="Available",Cell2>=1),,)

Replace Cell1 / Cell2 with your cell references of the cells returning those values. As you didn't give any information which cell are housing your 2 formulas in your first post I was assuming you were looking for one unified formula that uses the logic of your 2 given formulas and returns TRUE / FALSE.

 

[Scott82]

You're a star, It's working fine! Sorry for not explaining as well as you needed!

Thank you!

 

[konew1]

the last part of your formula is ...Shift Requirements'!$C$29:$C$30)>=1,TRUE,FALSE)

change it to

...$C$29:$C$30>=1),TRUE,FALSE)