Hi Harlan!

I enterd the reformatted formula (using CSE) and dtill got #REF errors. It
does not like the initial cell range in each of the named ranges:

Range1: $A$5:$A$9 - this is the list of worksheet names which feeds the
INDIRECT argument. These are arranged vertically, one name on each row. This
is the second part of the named range and is the list of projects located on
each of the worksheets referenced in A5:A9 : $a$50:$a$60. These are also
vertical.

Range2 is the same for the first part. The second part is the range from
which the formula is to select entries: $e$50:$e$60.

On some sheets containing the formula in question, all the cells for A5:A9
are filled; on others, only one or two may be filled...the blanks ones are
for future use. These sheets are in a standard format, so there will not be
a need to customize the sheet every time a new one is created.

When I go through the Formula Evaluation, when I get to the part where it
reviews the A5:A9, I get a #VALUE for the cell with an entry, and then #REF
errors for the blank cells. Of course, at the end, the formula returns the
#REF error. As the A5:A9 range is in both Range1 and Range2 (so the same
worksheets are selected), I get the #VALUE for the cell with an entry, and
then #REF errors for the blank cells in the OFFSET argument as well.

I checked all the cell ranges in the Named Range. All match, with the only
difference being the range from which to select the value in Range2 is in
Column E.

Range1 - "'"$A$5:$A$9&"'!$a$50:$a$60"

Range2 - "'"$A$5:$A$9&"'!$e$50:$e$60"

Here are the actual range entries from the workbook, from the Define Name
entries for the workbook. The only difference is the active worksheet name
that Excel inserts:

Range1 - "'"&WorksheetA!$A$166:$A$170&"'!$a$221:$a$245"

Range2 - "'"&WorksheetA!$A$166:$A$170&"'!$e$221:$e$245"

Thanks again for looking at this.

Greg
--
Greg


"Harlan Grove" wrote:

Greg in CO wrote...
....
When I put it on another worksheet (same format as the original) and had
only one worksheet listed in the cells for Range 1, no values returned. I
got a #REF error (actually displayed as there was no ISERROR arguement).
When I did a formula eval, as i stepped through, it showed #REF errors for
each blank cell.

I did make sure I entered the array with CSE.

Ideas?
....

[reformatted]
=SUM(
IF(
ISNUMBER(ROWS(INDIRECT(Range1))),
SUMIF(INDIRECT(Range1),
$A10,OFFSET(INDIRECT(Range2),,COLUMNS($A:A)-1))
)
)

I had tested this before posting it. If cells in the range of
worksheet names used to define Range1 are blank, then the ISNUMBER
call should return FALSE. That's what happens on my machine. As long
as the lists of worksheet names used to define Range1 and Range2 are
the same AND DERTICALLY ORIENTED (i.e., single column, multiple rows),
ISNUMBER and SUMIF should both return vertical 1D arrays with the same
number of entires, and whenever ISNUMBER is FALSE, the out SUM call
would ignore the corresponding SUMIF entry.

My formula could break down if the worksheet lists used to define
Range1 and Range2 differ OR are horizontally oriented.

So it should work under the assumptions I've now stated explicitly.
Provide details of how Range1 and Range2 are defined in the worksheet
where it doesn't work. Also, though this isn't likely to be the cause,
if any of the cells in the OFFSET(Range2,...) ranges evaluate to #REF!
themselves, my formula would also properly evaluate to #REF!.