Deepak,

I'm not sure what those named ranges refer to. Can you provide more
details using exact refernces?

agarwaldvk wrote:
Aladin

That's great! I now understand!

Two, what about the last bit of my query where the single cell instead
of being specified as A1 gets referred to by the use of the combination
of the 'Index() and the Match() functions like so :-

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


Any clues on this????????


Best regards



Deepak Agarwal



--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

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