# Re: Sumproduct Indirect Named Dynamic Range using Offset

*From*: Toppers <Toppers@xxxxxxxxxxxxxxxxxxxxxxxxx>*Date*: Sat, 5 May 2007 12:09:00 -0700

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.

"Frank Hayes" wrote:

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

"Toppers" <Toppers@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message

news:B6741095-53D7-4279-91D2-3DC73BDB67A5@xxxxxxxxxxxxxxxx

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

"Frank Hayes" wrote:

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

**References**:**Sumproduct Indirect Named Dynamic Range using Offset***From:*Frank Hayes

**Re: Sumproduct Indirect Named Dynamic Range using Offset***From:*Frank Hayes

- Prev by Date:
**Grading function in Excel** - Next by Date:
**Re: Clarification: Finding/Listing Symbols Found in 2 Columns** - Previous by thread:
**Re: Sumproduct Indirect Named Dynamic Range using Offset** - Next by thread:
**Re: Sumproduct Indirect Named Dynamic Range using Offset** - Index(es):