Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display filter value on chart (2003)

    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?
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display filter value on chart (2003)

    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.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display filter value on chart (2003)

    Excellent as usual, Hans. Thanks very much.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display filter value on chart (2003)

    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.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Display filter value on chart (2003)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •