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,OF FSET(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!

--
Greg