Sumproduct Indirect Named Dynamic Range using Offset

  • Thread starter Thread starter Frank Hayes
  • Start date Start date
F

Frank Hayes

I am trying to use the Sumproduct function on a named range using the
Indirect function. When the named range is a fixed range, it works fine.
When I try to make the range dynamic using Offset, Excel returns "#REF!"
instead of the value.

Here is a simplified illustration of what I am trying to do:

On Sheet1, A2 to A5 contains Sales Region names North, South, East, and
West. Row 1 column B to M is Month Name, Jan to December. Sales are shown
for each Sales Region in each Month in the appropriate cell.

Cell B8 contains a number that corresponds to the current month. In this
example, I am interested in data through March, so the value is 3.

Cell B10 contains a dropdown that is based on a list of 2 possible values.
The first value is "QTR1" and the second value is "YTD".

"QTR1" is a named range for the sales in Jan, Feb, and Mar defined as :
=Sheet1!$B$2:$D$5
"YTD" is a named range defined as : =OFFSET(Sheet1!$B$2,0,0,4,Sheet1!$B$8)

Cell B12 contains the formula: =SUMPRODUCT(INDIRECT(B10))

When I choose "QTR1" from the dropdown, the function returns the number.
However, when I choose "YTD" from the dropdown, Excel returns "#REF!". Can
someone point me in the right direction?

Thanks

Frank Hayes
 
Would this be OK in B12:

=IF(B10="YTD",SUMPRODUCT(OFFSET(Sheet1!$B$2,0,0,4,Sheet1!$B$8)),SUMPRODUCT(INDIRECT(B10)))

Assumes QTR1, QTR2 are fixed ranges.

HTH
 
That does work and thank you for the suggestion. I think I will play with
Indirect a bit and see if there is anyway to fool it with a Dynamic named
range.

Frank
 
Don't spend any time with INDIRECT and dynamic ranges: it simply doesn't work.

I am sure Biff would have corrected me if it did as he is extremely
knowledgeable (more so than I) about Excel. He has given you an alternative
solution.
 
Just to throw out another way it could be done

=SUM(YTD*(B10="YTD"),QTR1*(B10="QTR1"))

array entered using Cntl+Shift+Enter
 
Back
Top