#value returned when using spefic file path in defined name

I had to find a way to scale back the size of my formula due to the fact the it cannot exceed 8192 characters. From What I can see my file patch is correct in the name manager.
="'F:\Public Files\growers\Chemical Record Keeping\MAD Chemicals\[MAD Chemical Tracking.xls]".

When I click on the insert function button it looks like my formula is ok.
=SUMIFS('[=MADFileLocation]Template'!$H$1:$H$65536,'[=MADFileLocation]Template'!$A$1:$A$65536,">="&Inventory!$M$5,'[=MADFileLocation]Template'!$A$1:$A$65536,"<="&Inventory!$M$6,'[=MADFileLocation]Template'!$F$1:$F$65536,Inventory!$A8)
The formula works fine using the absolute file path.

This has been bugging me for days. I have a work around, but I would love to get this right. I am no programmer, nor do I play one on TV. Just a self taught excel geek.

Thanks for your reply TMS. That did not correct it. The original formula is a large Sumifs that pulls data from up to 3 columns in 4 different workbooks. I need to add in a 5th workbook, which works fine until I turn it into an If(Iserror statement. Then it exceeds the maximum character length, due to length of the file path name when all workbooks are closed.

SUMIFS or SUMIF formulas do not function properly with closed external files.
Try:

Formula:

Please Login or Register  to view this content.

Robertswart.
SUMPRODUCT will not work. The SUM range H:H is a numeric field. Range A:A is a date. M5 and M6 are the month long date ranges. Range F:F is text as well as A8 is the text I want to match.

Will not or does not? It should work, also with dates and texts.