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:B674109553D7427991D23DC73BDB67A5@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
 Sumproduct Indirect Named Dynamic Range using Offset
 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):
Relevant Pages
