Aladin

Sorry not providing this information!

In the formula

{=sum(sumif(INDEX(Index_200407_NSW,MATCH(TRIM($A9) ,
TRIM(MatchCol_200407_NSW),0),1)), "<0", "0", "0"))} - formula being
array entered

the named ranges are in external workbook, located in the folder
"j:\dds\reports\monthly\200407\"

The name of the workbook is "Latest Monthly Report"

The named range Index_200407_NSW refers to the range "A1:G88" on the
NSW worksheet.

The named range MatchCol_200407_NSW refers to the range "C1:C88" on the
same NSW worksheet.


The idea of doing this whole exercise was to be able to read a
particular in a closed external workbook. The Index() function, as you
know, does this perfectly.
No worries here.

But when

INDEX(Index_200407_NSW,MATCH(TRIM($A9)
,TRIM(MatchCol_200407_NSW),0),1))

this returned an error value (when the search entry is not found), I
can have the return value expressed as a '0' by using
--(iserror(Index(...))) or a Blank ("") using an if clause, that's ok!

But if I have a '0' for all error values, I will have a lot many zeroes
that don't look very good when it goes to the board. It suits better in
this case if I have it expressed it (the error value) as a blank but
the problem is that when I try and add this blank subsequently to
another value it obviously returns an error value (trying to add a
number to a blank (nonnumeric value)!!!)

So what I was looking at doing was try an encapsulate this Index()
function in a sum(sumif()) type function, as you suggested, and it
worked also so long as the referred workbooks were open - which in this
scenario is not feasible (there are 24 of them). The target workbook is
a template workbook for this report with automation including automatic
range names creation for formulas and graphs etc.etc.

Any further suggestions on this!



Best regards


Deepak Agarwal


--
agarwaldvk
------------------------------------------------------------------------
agarwaldvk's Profile: http://www.excelforum.com/member.php...o&userid=11345
View this thread: http://www.excelforum.com/showthread...hreadid=384426