#REF Error in Named Range with INDIRECT, SUMPRODUCT, and OFFSET

[Greg in CO]

Hi All!

I have run into an odd issue using a Named Range with INDIRECT, SUMPRODUCT,
and OFFSET.

I have a worksheet that has a formula which will look for other worksheets
with names that are listed on the worksheet and once it finds the worksheet,
the formula then looks for a Project name on that worksheet and returns the
number in the corresponding cell.

The formula uses SUMPRODUCT, INDIRECT, OFFSET, AND IF(ISERROR).

The formula is as follows:

=IF(ISERROR(SUMPRODUCT(SUMIF(INDIRECT(Range1),$A10,OFFSET(INDIRECT(Range2),,COLUMNS($A:A)-1)))),"",SUMPRODUCT(SUMIF(INDIRECT(Range1),$A10,OFFSET(INDIRECT(Range2),,COLUMNS($A:A)-1))))

Breakdown:

Range1= "'"$A$5:$A$9&"'!$a$50:$a$60" (A5:A9 = a list of other worksheet
names; A50:A60 = a list of project on those worksheets)

Range2= "'"$A$5:$A$9&"'!$e$50:$e$60" (A5:A9 = a list of other worksheet
names, as above;E50:E60 = the hours total for each project - per row - for a
month)

A10 is the project name on the current worksheet; I want the formula to look
for this name on the other sheets, to return the corresponding value for the
month in question.

OFFSET is in the formula to allow the formula to be used across muliple
columns representing a year, and thus returning the hour total for the same
month on the other worksheets. The month columns are in the same columns on
all sheets.

Sooooooooooo....when A5:A9 are all filled with names, the formula works like
a charm (many thanks to the Excel gurus here for helping me learn about these
complex formulae)...but when any of the cells in A5:A9 are blank, the formula
does not return any values. Using the Formula Auditing function and going
through the evaluation, as I step thorugh the formula, I get #REF errors for
the blank cells.

As soon as I put in any data, the formula works great.

Is there way to make this formula work and ignore the blank cells? I have a
cell range there, as some worksheets will have several worksheets listed
(filling the range) and others will have only 1 subordinate worksheet listed.
I did not want to create a custom sheet each time the number of subordinate
worksheets is different.

All help is appreciated!!!!!!!

The folks here have been great and the info I have found without even having
to post questions has been super!

Thanks again!

 

[Harlan Grove]

The formula is as follows:

=IF(ISERROR(SUMPRODUCT(SUMIF(INDIRECT(Range1),$A10,OFFSET(INDIRECT(Range2),,COLUMNS($A:A)-1)))),
"",SUMPRODUCT(SUMIF(INDIRECT(Range1),$A10,OFFSET(INDIRECT(Range2),,COLUMNS($A:A)-1)))) ....
Sooooooooooo....when A5:A9 are all filled with names, the formula works like
a charm (many thanks to the Excel gurus here for helping me learn about these
complex formulae)...but when any of the cells in A5:A9 are blank, the formula
does not return any values.  Using the Formula Auditing function and going
through the evaluation, as I step thorugh the formula, I get #REF errors for
the blank cells. ....
Is there way to make this formula work and ignore the blank cells?

....

You have to use an array formula.

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

 

[Greg in CO]

Thanks Harlan! The formula you provided worked as the original one and
returned values when all cells in the Range 1 (worksheet list) had entries.

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?

Thanks!

 

[Harlan Grove]

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!.

 

[Greg in CO]

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

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!.

 

[Harlan Grove]

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:

....

Looks like I didn't test thoroughly. Try the following instead.

=SUM(IF(ISNUMBER(COUNTIF(INDIRECT(Range1,$A10)),
SUMIF(INDIRECT(Range1),
$A10,OFFSET(INDIRECT(Range2),,COLUMNS($A:A)-1))))

 

[Greg in CO]

Yay! I entered the formula you updated...Excel made a correction (a missing
parenthese, I beleive) and the formula works. I went through the
Evaluator...as I got the end where it shows the values avaialble after doing
to look up and comapre, it showed 100, False, False, False, False for the
final range, which is correct, as there was only one worksheet name entry in
the worksheet name range. During the eval, I was still getting the #VALUE,
#REF sequence, but the current structure ignores the #REF errors and returns
the one value it finds: 100.

Thanks!

You get today's "You rock!" award...feel free to tell yourself you rock!

Greg