Excel Chart Will Not Accept Named Range Based on INDIRECT Function

[rkaczano]

I have a range that I have built up using the address function:


A1 = the cell range $A$37:$A$780
A2= is the existing sheet name = Profile
A3 = is the full range including the sheet and cell range 'Profile'!$A$37:$A$780

And I know the range in A3 works as I can put it in a SUM formula and the answer is correct.

=SUM(INDIRECT(A3)) = a number

I now want to pass the range in A3 to a Chart. I know I cannot pass a cell address expressed as text to a chart series. So I created a named range and wrapped the cell address expressed as text in A3 as follows:
Name: = ChartRangeVariable
Refers to: =INDIRECT('Profile'!$A$3)

However the chart will not accept that named range. Is this not possible?

Thanks

 

[Jeffrey Mahoney]

I would create a named range for a range that gets the data from other ranges based on a cell value.

Lets say you have 10 ranges that could potentially be the data for the chart. Put formula(s) in a single location that read the data from any of those 10 ranges based on the contents of a single cell. Make that range a Named Range that you reference in your chart. Change the cell value to reference a different range and the chart is updated.

 

[rkaczano]

Thanks, I need you to elaborate on that a little more.

Lets say I know the following below. There are 12x ranges defined by the table below (e.g. the first range is $A$37:$A$780. My Chart needs to graph the data from that range. I am trying to provide the chart with this range using a named range with an INDIRECT formula. Are you saying that I should create a large 2x2 table with cells that pull data form all those ranges and then have a dynamic named range (using OFFSET) for the column I want and then have the graph feed off of that named range?

$A$37	$A$781	$A$1453	$A$2197	$A$2917	$A$3661	$A$4381	$A$5125	$A$5869	$A$6589	$A$7333	$A$8053
$A$780	$A$1452	$A$2196	$A$2916	$A$3660	$A$4380	$A$5124	$A$5868	$A$6588	$A$7332	$A$8052	$A$8796

 

[Jeffrey Mahoney]

So, in this example, I added a series of numbers in column A as dummy data. The formula in Cell P2 spills down according to the cell references in rows 1 and 2. I Named the Range from P2 down is a dynamic named range that changes in size according to the number of rows of returned data. Cell N2 is where you choose your values. Changing this changes which column of cell references you want to use. The chart will reference the named range Chart_Series_Values

test.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	$A$37	$A$781	$A$1453	$A$2197	$A$2917	$A$3661	$A$4381	$A$5125	$A$5869	$A$6589	$A$7333	$A$8053		Series		Chart Series Values
2	$A$780	$A$1452	$A$2196	$A$2916	$A$3660	$A$4380	$A$5124	$A$5868	$A$6588	$A$7332	$A$8052	$A$8796		2		842
3																842
4																842
5	Values for Chart															843
6	2	1														843
7	3	2														843
8	3	3														844
9	6	4														844
10	8	5														849
11	9	6														850
12	9	7														851
13	10	8														853
14	10	9														854
15	10	10														856
16	10	11														857
17	12	12														860
18	13	13														861
19	13	14														861
20	14	15														862
21	14	16														863
Sheet6

Cell Formulas
Range	Formula
P2:P673	P2	=INDIRECT(INDEX(ChartRangeRef,1,SeriesChoice)&":"&INDEX(ChartRangeRef,2,SeriesChoice))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
ChartRangeRef	=Sheet6!$A$1:$L$2	P2
SeriesChoice	=Sheet6!$N$2	P2

 

[rkaczano]

Yes that worked - thank-you.