Setting Chart X and Y values indirectly

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to set X-Values in my chart in a cell of a worksheet, so that
when the range of my x-values changes I will not have to go into the wizard
and type it again.

For example, I want to use the following X-values in my chart:

=DATA!$I$2:$I$20

But instead of typing the above in the X Values: field in the 'Source Data /
Series' wizard, I would like to type it, say, in cell $A$1 of 'Sheet1', and
then type in the X Values field in the 'Source Data / Series' wizard the
following:

=Sheet1!$A$1

I tried it and it does not work. Can you please let me know if there is a
way to achieve what I am trying to do?

Thanks.
 
Define a name (Insert menu > Names > Define) that uses the definition of the
range:

Name: MyXRange
Refers To:
=DATA!$I$2:$I$20

Then in the source data dialog, you can enter =DATA!MyXRange in the X values
field. You can use formulas to make the definition of MyXRange change
according to how the data itself changes. This technique is referred to as
'Dynamic Charting'. See some examples and links here:

http://peltiertech.com/Excel/Charts/Dynamics.html
http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html

- Jon
 
Back
Top