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 |
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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 08:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com