Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Indirect Named Dynamic Range using Offset
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Indirect to Named range | Excel Worksheet Functions | |||
named range row offset | Excel Worksheet Functions | |||
Dynamic named range & Offset | Excel Discussion (Misc queries) | |||
Offset delivers value error with Named range | Excel Worksheet Functions | |||
named range, offset self-reference | Excel Discussion (Misc queries) |