Dynamically changing the series of chart

**bhokalivarun** · 06-23-2009, 03:54 PM

I am getting the X-Values of the series as follows:

='Input'!$A$10:$Z$10

I need to get the number 10 in the above syntax from a cell say B20. How should i change the syntax to get this to work.

-Varun

**sweep** · 06-24-2009, 04:48 AM

You'll have to define a named range, and use that in your graph.

Define the named range as follows:

=INDIRECT("Input!$A$"&Input!$B$20&":$Z$"&Input!$B$20)

**bhokalivarun** · 06-24-2009, 10:56 PM

Thanks for the help Dave. But I get an error "The function is not valid".

-Varun

**shg** · 06-24-2009, 11:26 PM

Sweep's syntax looks good to me. Post a workbook

**bhokalivarun** · 06-25-2009, 03:55 AM

I have attached a sample spreadsheet. I need to plot on sheet 'graph'
A1:L1 on x-axis vs A2:L2 on y-axis. The "2" here in the Y-values comes
from B6.

I am using the following:

=INDIRECT("Input!$A$"&Input!$A$6&":$L$"&Input!$A$6)

and getting the error : "The function is not valid"

-Varun

**DonkeyOte** · 06-25-2009, 04:02 AM

Try creating a Name as follows:

Name: ChtY
RefersTo: =INDEX(Input!$A$1:$L$3,$A$6,0)

Now in your chart create the Series:

Series Name: "whatever"
Series Values: =Input!ChtY

As you alter A6 so the Chart should update accordingly.