How many parentheses?

[Scott82]

Hi all

I've had help from the board to make this:

=TRIM(IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Giles House\2010 - 2011\[Giles House Term 6 2010-2011.xls]Sheet1'!B3:B13,0)),"","Giles"))&""&IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Millington House\2010 - 2011\[Millington House Term 6 2010-2011.xls]Sheet1'!B3:B12,0)),"","Millington"))&""& IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Shirley House\2010 - 2011\[Shirley House Term 6 2010-2011.xls]Sheet1'!B3:B11,0)),"","Shirley"))&""&IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Palmer House\2010-2011\[Palmer Term 6 2010-2011.xls]Sheet1'!B3:B12,0)),"","Palmer"))&""& IF(ISNA(MATCH("Julie",S:\School\SMT\Rotas\Fleming House\2010 - 2011\[Fleming House Term 6 2010-2011.xls]Sheet1'!B3:B12,0)),"","Fleming"))&""&IF(ISNA(MATCH("Julie",S:\School\SMT\Rotas\Todhunter House\2010-2011\[Todhunter House Term 6 2010-2011.xls]Sheet1'!B3:B14,0)),"","Todhunter"))&""&IF(ISNA(MATCH("Julie",S:\School\SMT\Rotas\Charlotte House\2010-2011\[Term 6.xls]Sheet1'!B3:B13,0)),"","Charlotte"))&""&IF(ISNA(MATCH("Julie",S:\School\SMT\Rotas\Day Staff Overtime Availability\School\[Education rota Term 6 2011.xls]Sheet1'!B3:B13,0)),"","School"))

But I haven't got the parentheses right, what am I missing??

Many thanks

Scott

 

[wigi]

I would first test this with a file called "a.xls" that you store at "C:\" level. Can't have it shorter than that... Or open the source file, so that paths are omitted.

In front of S:\School\, several times, you're missing single quotation marks.

Also, chop the formula in different cells and once it's working, piece it together if you really really really want to see this in once cell without helper cells.

 

[jasonb75]

Dupicate thread http://www.mrexcel.com/forum/showthread.php?t=556686

 

[GlennUK]

=IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Giles House\2010 - 2011\[Giles House Term 6 2010-2011.xls]Sheet1'!B3:B13,0)),"","Giles")&IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Millington House\2010 - 2011\[Millington House Term 6 2010-2011.xls]Sheet1'!B3:B12,0)),"","Millington")&IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Shirley House\2010 - 2011\[Shirley House Term 6 2010-2011.xls]Sheet1'!B3:B11,0)),"","Shirley")&IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Palmer House\2010-2011\[Palmer Term 6 2010-2011.xls]Sheet1'!B3:B12,0)),"","Palmer")& IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Fleming House\2010 - 2011\[Fleming House Term 6 2010-2011.xls]Sheet1'!B3:B12,0)),"","Fleming")&IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Todhunter House\2010-2011\[Todhunter House Term 6 2010-2011.xls]Sheet1'!B3:B14,0)),"","Todhunter")&IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Charlotte House\2010-2011\[Term 6.xls]Sheet1'!B3:B13,0)),"","Charlotte")&IF(ISNA(MATCH("Julie",'S:\School\SMT\Rotas\Day Staff Overtime Availability \School\[Education rota Term 6 2011.xls]Sheet1'!B3:B13,0)),"","School")

... I took out the rubbish.

 

[Scott82]

Thank you! I've just found the formula is too long........
I'm going to have to split it. God dam!

Thanks to all who have helped!