update chart after filter

I am stumped! I need to update a multiple charts based off of the data that
is filtered... column A i have "x" entered, and the filter is set to display
only the fields with "x". Ex:
A B C D
x Feb 60%
x Mar 88%
x 3/01 100%
x 3/02 75%

I need the charts to update when I go to enter "x" for the new month and
delete them for the previous month and refresh the filter. The chart
displays the month values as bars & day values as a line chart.

Hi,

I am not sure that I really understand your question or if maybe there is a
better way of achieving your goal.

Charts update themselves automatically when the AutoFilter is changed.
Provided that you have selected the entire AutoFilter range for the chart’s
data series, the chart updates to the visible range.

Your example appears to display 2 totally different types of data in column
B; One set for the Bars and another set for the line. I don’t think that
Excel can determine which values to apply to the Bars and which to apply to
the line unless you adjust them manually in the chart.

Initially you say "I need to update a multiple charts" and then further down
you say "The chart displays the month values as bars & day values as a line
chart". Is it one chart with Bars and Line or 2 separate charts?

I think that you need to separate the data into 2 tables so that you have
the Bar chart series data in one table and the Line chart series data in
another table. If using AutoFilter then place the tables on separate
worksheets. You can select data from one worksheet for one series on the
chart and select data from another worksheet for another series. (You select
the entire AutoFilter range for the chart series and the chart displays only
the visible cells after AutoFilter is applied.)

Just as an added extra, in lieu of having to insert X's against all the data
to display, if I have a column of dates (say column B) that I use in a chart
and I want to be able to filter on a particular month then in column A I
insert the following formula:-

=DATEVALUE(TEXT(B2,"mmm yy"))

Copy the formula down for the full length of the data and then simply set
the filter to the required month. You can just use "mmm" for the format so it
does not display year if so desired. You can also set a custom filter to
filter between 2 dates but I find my method quick and easy to use.


--
Regards,

OssieMac


"gixer" wrote:

I am stumped! I need to update a multiple charts based off of the data that
is filtered... column A i have "x" entered, and the filter is set to display
only the fields with "x". Ex:
A B C D
x Feb 60%
x Mar 88%
x 3/01 100%
x 3/02 75%

I need the charts to update when I go to enter "x" for the new month and
delete them for the previous month and refresh the filter. The chart
displays the month values as bars & day values as a line chart.

I have a similar problem, My charts update fine except when there is only one
point of data. Then for some reason the X axis scale "minor unit" changes,
and the plot area displays as all black. I resolved this by setting my
trendline options to predict forward 1 unit, but just for curiosity, is there
a better way to set axis scale options automatically to handle single data
points?

Here's some detail about my chart settings, in case it helps:
My advanced filter selects for a specified year or years as entered in the
criteria field (the actual filter runs onClick of a macro button). I want
the major units set to 90 days (i.e., annual quarters). Since the user
selects 1-5 different years, the axis scale Min & Max & Y intercept are
automatic. The minor unit is not automatic, but it changes to 82.4397 when
there's only 1 data point (UNLESS the trendline is set to predict). When
this happens, the result is a blacked out plot area (actually many vertical
"gridlines") and a blank X axis (no values) - but the point is plotted and
shows the values on hover.

"OssieMac" wrote:

Hi,

I am not sure that I really understand your question or if maybe there is a
better way of achieving your goal.

Charts update themselves automatically when the AutoFilter is changed.
Provided that you have selected the entire AutoFilter range for the chart’s
data series, the chart updates to the visible range.

Your example appears to display 2 totally different types of data in column
B; One set for the Bars and another set for the line. I don’t think that
Excel can determine which values to apply to the Bars and which to apply to
the line unless you adjust them manually in the chart.

Initially you say "I need to update a multiple charts" and then further down
you say "The chart displays the month values as bars & day values as a line
chart". Is it one chart with Bars and Line or 2 separate charts?

I think that you need to separate the data into 2 tables so that you have
the Bar chart series data in one table and the Line chart series data in
another table. If using AutoFilter then place the tables on separate
worksheets. You can select data from one worksheet for one series on the
chart and select data from another worksheet for another series. (You select
the entire AutoFilter range for the chart series and the chart displays only
the visible cells after AutoFilter is applied.)

Just as an added extra, in lieu of having to insert X's against all the data
to display, if I have a column of dates (say column B) that I use in a chart
and I want to be able to filter on a particular month then in column A I
insert the following formula:-

=DATEVALUE(TEXT(B2,"mmm yy"))

Copy the formula down for the full length of the data and then simply set
the filter to the required month. You can just use "mmm" for the format so it
does not display year if so desired. You can also set a custom filter to
filter between 2 dates but I find my method quick and easy to use.


--
Regards,

OssieMac


"gixer" wrote:

I am stumped! I need to update a multiple charts based off of the data that
is filtered... column A i have "x" entered, and the filter is set to display
only the fields with "x". Ex:
A B C D
x Feb 60%
x Mar 88%
x 3/01 100%
x 3/02 75%

I need the charts to update when I go to enter "x" for the new month and
delete them for the previous month and refresh the filter. The chart
displays the month values as bars & day values as a line chart.