Dynamic Series Name

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

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

"ArthurJ" <ArthurJ@discussions.microsoft.com> wrote in message
news:CBCFAD71-EFBD-4701-8DA8-A5CA3721CFBC@microsoft.com...
>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