Re: Sumproduct Indirect Named Dynamic Range using Offset



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






.



Relevant Pages

  • Re: Sumproduct Indirect Named Dynamic Range using Offset
    ... Indirect a bit and see if there is anyway to fool it with a Dynamic named ... When the named range is a fixed range, ... for each Sales Region in each Month in the appropriate cell. ... Cell B8 contains a number that corresponds to the current month. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: External Reference with INDIRECT, INDEX/MATCH
    ... INDIRECT requires a *text* representation of a valid reference. ... Assume cell A1 is your drop down list. ... If you have only that single named range: ... Prior to Excel 2007 value arguments = 29 ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Indirect and dynamic ranges
    ... >INDIRECT only works with cell references and ranges, ... >>I'm having trouble using a dynamic named range through ...
    (microsoft.public.excel.worksheet.functions)
  • Re: corrupted formula in protected worksheet
    ... Any idea why the name of the named range no longer shows up in the worksheet ... name box when 'Refers to' does employ INDIRECT? ... "Dave Peterson" wrote: ... formulas dependent on that name adopt the value in the pasted cell. ...
    (microsoft.public.excel.misc)
  • Re: INDIRECT
    ... SearchValue is the value the lookup us looking for in 1st column of ... INDIRECT returns a range reference, which can be used as range parameter ... read from some cell, or be calculated or entered directly. ... It returns a sum of sells A1:A10 from sheet, ...
    (microsoft.public.excel.worksheet.functions)