XL2000 Array Function Problem

[LPS]

Using XL2000, I have 12 identical workbooks, one per case worker. Each of
these workbooks tracks the types of incidents the case workers deal with.
The types of incidents are standard across all workbooks.

These 12 workbooks roll-up into a summary workbook for the supervisor. The
summary workbook has 12 columns, one for each case worker, and about 15 rows,
one for each type of incident. I have created an array SUMPRODUCT function
which counts the number of each type of incident for each case worker. This
function counts based on the existence of 3 criteria: whether there is a
client name in column B, the type of incident entered in column F and if the
status in column N is "Active" or "Closed". For example:

{=SUMPRODUCT(--('H:\Training\User Requests\2008\Sharon Thompson\[New CAWL -
Cloutier, Teresa.xls]Case
Activity'!$N$17:$N$1000="Active")*(--('H:\Training\User Requests\2008\Sharon
Thompson\[New CAWL - Cloutier, Teresa.xls]Case
Activity'!$F$17:$F$1000="oca")*(--('H:\Training\User Requests\2008\Sharon
Thompson\[New CAWL - Cloutier, Teresa.xls]Case
Activity'!$B$17:$B$1000<>""))))}

I created the first function and then copied it to the remaining cells,
modifying the case worker name and incident type, as required. Everything
worked as expected until recently. For some reason, one instance of this
function has stopped calculating and constantly returns a "zero" value. I
have checked and re-checked that all the criteria exists and the function
should return a result of something like 58. The functions above and below
this one, both calculate. I have tried copying the working functions to this
particular cell (and of course, modifying the resulting copied function to
look for the correct data) but it still returns a "zero" result. I have
manually checked the contributing worksheet and there is data in it that
should be calculated. I have tried editing (F2) and pressing ENTER, to no
avail. I have retained the array quality (actually tried it both ways) to no
avail.

Can anyone suggest why a function like the one above would work as expected
and then just stop. It is like the cell itself has died. I have tried
reformatting the cell, deleting the cell and inserting a new cell… nothing
seems to help.

Perhaps some one out there can???? All suggestions and help are greatly
appreciated.

 

[Pete_UK]

It might be that in your sending workbook you have typed "Active " or
"oca " in those columns (i.e. with a space at the end), and then
autocomplete has propagated this down the column(s) when you have
added new entries. Check them out using =LEN(N17) and =LEN(F17) etc on
those columns.

Hope this helps.

Pete

 

[LPS]

Thank you for the suggestion; I will check it out. I should have also added
that those fields (case type and acitve vs, closed) are populated through
validation lists so the client can't type freely in them.

If you can think of anything else, I'm open to all suggestions.
--
LPS

It might be that in your sending workbook you have typed "Active " or
"oca " in those columns (i.e. with a space at the end), and then
autocomplete has propagated this down the column(s) when you have
added new entries. Check them out using =LEN(N17) and =LEN(F17) etc on
those columns.

Hope this helps.

Pete

Using XL2000, I have 12 identical workbooks, one per case worker. Each of
these workbooks tracks the types of incidents the case workers deal with.
The types of incidents are standard across all workbooks.

These 12 workbooks roll-up into a summary workbook for the supervisor. The
summary workbook has 12 columns, one for each case worker, and about 15 rows,
one for each type of incident. I have created an array SUMPRODUCT function
which counts the number of each type of incident for each case worker. This
function counts based on the existence of 3 criteria: whether there is a
client name in column B, the type of incident entered in column F and if the
status in column N is "Active" or "Closed". For example:

{=SUMPRODUCT(--('H:\Training\User Requests\2008\Sharon Thompson\[New CAWL -
Cloutier, Teresa.xls]Case
Activity'!$N$17:$N$1000="Active")*(--('H:\Training\User Requests\2008\Sharon
Thompson\[New CAWL - Cloutier, Teresa.xls]Case
Activity'!$F$17:$F$1000="oca")*(--('H:\Training\User Requests\2008\Sharon
Thompson\[New CAWL - Cloutier, Teresa.xls]Case
Activity'!$B$17:$B$1000<>""))))}

I created the first function and then copied it to the remaining cells,
modifying the case worker name and incident type, as required. Everything
worked as expected until recently. For some reason, one instance of this
function has stopped calculating and constantly returns a "zero" value. I
have checked and re-checked that all the criteria exists and the function
should return a result of something like 58. The functions above and below
this one, both calculate. I have tried copying the working functions to this
particular cell (and of course, modifying the resulting copied function to
look for the correct data) but it still returns a "zero" result. I have
manually checked the contributing worksheet and there is data in it that
should be calculated. I have tried editing (F2) and pressing ENTER, to no
avail. I have retained the array quality (actually tried it both ways) to no
avail.

Can anyone suggest why a function like the one above would work as expected
and then just stop. It is like the cell itself has died. I have tried
reformatting the cell, deleting the cell and inserting a new cell… nothing
seems to help.

Perhaps some one out there can???? All suggestions and help are greatly
appreciated.