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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Indirect Named Dynamic Range using Offset
INDIRECT cannot refer to a dynamic range as it is not an actual address.
"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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Indirect Named Dynamic Range using Offset
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Indirect Named Dynamic Range using Offset
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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Indirect Named Dynamic Range using Offset
Another way:
=SUM(CHOOSE(MATCH(B10,{"Qtr1","YTD"},0),Qtr1,YTD)) Biff "Frank Hayes" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sumproduct Indirect Named Dynamic Range using Offset
Just to throw out another way it could be done
=SUM(YTD*(B10="YTD"),QTR1*(B10="QTR1")) array entered using Cntl+Shift+Enter "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 |
Reply |
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) |