link a chart source to a cell reference

[Nick]

Hi, lets say i have a cell A1 that shows the text B1:B5, and i have a cell A2
that shows the text C1:C5. Then i want to have a chart that references cells
A1 and A2, and hence knows that i want it to chart the data in arrage B1 to
C5 (an x-y scatter plot). the point being the text values in cells A1 and A2
might change, and i want to the chart to automatically update and plot the
new ranges specified. i can't work out how to do this. is it possible?

thanks

 

[Jon Peltier]

Define names. Go to Insert menu > Names > Define

Enter the following:

Name : myX
Refers To: =B1:B5
Click Add

Name: myY
Refers To: =C1:C5
Click Add

Close the dialog.

Create a chart using the regular range. Click on the series, look at the
series formula in the formula bar. Edit it to change this:

=SERIES(,Sheet1!$B$1:$B$5,Sheet1!$C$1:$C$5,1)

to this

=SERIES(,Sheet1!myX,Sheet1!myY,1)

Click Enter. If you redefine the names defined above, the chart will adjust
accordingly.

- Jon

 

[ShaneDevenshire]

Hi Nick,

I read your question to mean you want to run the chart off of INDIRECT(A1)
which would return the contents of the range B1:B5 and use those for the
chart? You can create a name myX which refers to =INDIRECT($A$1). However,
for some reason Excel is not happy with this when you add it to the chart.

So, if I understand your question correctly, I don't know if it is possible.

 

[ShaneDevenshire]

Hi Nick,

I have found a way, it doesn't use INDIRECT but it let's the chart plot from
a pair of cell addresses in cell A1:A2. It's rather complicated:

1. Define the following names - choose Insert, Name, Define and enter each
of the names below in the Names in workbook box and the formula in the Refers
to box:

Col1 (this formula figures out which column your X data is in but only
works out to column Z as I set it up):

=MATCH(LEFT(Sheet1!$A$1),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},0)

Col2 (this formula figures out which column contains your Y data - the
OFFSET function needs numbers, not letter):

=MATCH(LEFT(Sheet1!$A$2),{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},0)

H (this formula calculates the height argument for the OFFSET function):

=VALUE(RIGHT(Sheet1!$A$1,LEN(Sheet1!$A$1)-FIND(":",Sheet1!$A$1)-1))

L (this function figures out the starting row of your data)
=VALUE(MID(Sheet1!$A$1,2,FIND(":",Sheet1!$A$1)-2)-1

X
=OFFSET(Sheet1!$A$1,L,Col1-1,H)
Y
=OFFSET(Sheet1!$A$1,L,Col2-1,H)

Now follow Jon instructions for assigning the range names X and Y to the
chart.

1. Select the series on the chart and replace the reference after the !
with X or Y. In 2003 this will automatically change the sheet reference to a
workbook reference, in some earlier versions I believe you needed to type the
entire address over with the workbook name instead of the sheet name.

Now you can select cells A1 and A2 and type in the address as text and the
chart will plot automatically.

You could have defined the names H, L, Col1, and Col2 as spreadsheet cells
where the formulas returned their results.

I tried to substitute all the formula into the X and Y formulas but I either
made a typo or Excel was not happy with them, so I resorted to defining the 4
extra names.

If you need a copy of the workbook let me know.