Dynamic Series Name

G

Guest

I have a table that is set up using "Data-Filter-AutoFilter" so that the user
can click a drop down arrow in the header for column 1 (the x-values), select
a value, and the table displays only those records.

A chart is connected to the table.

In the chart I want the series name to be the user's choice of the dropdown
value. For example, say the x-value choices are "apples", "oranges", or
"lemons". If the user chooses "oranges", then that becomes the series name in
the chart.

Can someone come up with a way to do this. I have thus far done all this
without VBA, and prefer to leave it that way.

Thanks,
Art
 
E

Ed Ferrero

Hi Arthur,
If you open a chart and select one series (by single clicking it)
the formula bar will show something like this:

=SERIES(Label,X-Range,Y-Ranges,Series No)

Label is the series label, can be a cell reference or a
label enclosed in quotes
X-Range is a range, usually given in the form Sheet1!$C$5:$M$5
but it could also be a series of numbers such as {5,6,7}
or a reference to a range name such as Sheet1!Sales
Y-Range is similar
Series No is an integer giving the z-order of the series

Change the Label to a cell reference like Sheet1!C1 (You can do this by
selecting a cell).
If the cell reference is the same cell that is used by AutoFilter to select
"oranges" then you have what you want.

Ed Ferrero
http://edferrero.m6.net
 

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