Array Formula

I have an array formula that works well when in the same workbook, but what i want is to build a database that will read the main information from one workbook and use the formula in another workbook (Not sheet), it works fine in the workbook but not on seperate sheets, the formula is


={IF(ROWS(C$3:C3)>COUNTIF('All Monthly WIP By Team2'!$A$2:$A$76,$A$1),"",INDEX('All Monthly WIP By Team2'!C$2:C$76,SMALL(IF('All Monthly WIP By Team2'!$A$2:$A$76=$A$1,ROW('All Monthly WIP By Team2'!C$2:C$76)-ROW('All Monthly WIP By Team2'!C$2)+1),ROWS(C$3:C3))))}

i will add a copy of the workbook

When the workbook is open i get the values i requie, when closed i recieve the following

AGO

#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!
#VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! #VALUE!

Please if someone could help this would be most appreciated

Thanks

FBF

Also the main sheet that it is reporting from is attached

and this is the seperate sheet

The problem is probably COUNTIF. COUNTIF doesn't work with closed workbooks. Try replacing this part:

COUNTIF('All Monthly WIP By Team2'!$A$2:$A$76,$A$1)

with

SUMPRODUCT(--('All Monthly WIP By Team2'!$A$2:$A$76=$A$1))

i will try at work tommorow, thanks FBF