Display filter value on chart (2003)

Topic

New Reply

There is a list of items with a column titled “Collection”. I use an AutoFilter and the values in Collection to filter items for display, and to show the values of some other columns on a chart. For example, I can chart the data for “Beaver Cove”, or “Gold River”, or “Nanaimo”, etc. by using the AutoFilter. I want to display the current filter value on the chart. I am stumped at two places – to display the filter value in a particular cell on the spreadsheet, and how to display the value of that particular cell on the chart.

Is there a way to display the filter value on the chart?

Reply | Quote

Replies

1) Display autofilter criteria in cell:
See Excel User Tip: Displaying AutoFilter criteria for a custom function you can use to display autofilter criteria in a cell.

2) Display criteria on chart:
– Click anywhere in the chart.
– Type =
– Point to the cell that contains the autofilter criteria (see above).
– Press Enter.
– You now have a text box on the chart that displays the cell contents.
– You can drag the text box to a convenient location.

Note: you can also use the chart title for this:
– Use Chart | Chart Options to set an arbitrary chart title.
– Click on the chart title.
– Type =
– Point to the cell that contains the autofilter criteria (see above).
– Press Enter.

Reply | Quote

Excellent as usual, Hans. Thanks very much.

Reply | Quote

Slight addition to the custom function: Application.Volatile makes it recalculate whenever the filter is changed. Otherwise you must select the cell containing the formula, move the cursor to the formula bar, and press Enter.

Reply | Quote

It will only recalculate with a filter change if a formula result is changed. Typically one adds a SUBTOTAL calculation somewhere on the sheet to ensure that refiltering triggers a calculation. Without a subtotal formula, filtering may not trigger a recalc so the Application.volatile will do nothing

Steve

Reply | Quote