Setting Chart X and Y values indirectly

M

Manos

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.
 
J

Jon Peltier

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top