Filterable Dynamic Chart Range

  • My problem:
    Imagine I am plotting characteristics of a monopoly game: each row of my data represents one property and has a number of characteristics in columns. I have a scatter plot that draws several series from data in a filtered list. This lets me turn some of the points on and off by changing the filters, because the chart only plots the points that are visible. I'm remaking this chart using named ranges so that it will update each series automatically as the named ranges change. I'm doing this because I occasionally want to move datapoints (i.e. rows in the original data) from one series to another, and I don't want to manually go back and edit the series definitions in the chart. For example, if I am charting a monopoly game and each series represents one player, and each point is a property owned by that player, I want to account for the fact that properties may be traded between players.

    I got the chart all set up using named ranges so that each series has one named range for the X values and one for the Y values. These are defined to include or exclude points from the list (think of it as the list of all the properties in monopoly) and it works great... except when I use filters on the source data (for instance, filter out the yellow properties) it no longer removes those points from the chart. Because the chart refers to the named range and the named range is immune to the filtering, changes in filtering do not update the chart.

    My chart named ranges are defined like this:
    John_Properties_Cost = (All_Properties_Owner=$B$115)*Cost_Of_Property
    John_Properties_Rent = (All_Properties_Owner=$B$115)*Rent_For_Property

    I thought a good solution would be to "break up" the named range so that part of the calculation happens in the chart, like this:
    instead of

    named ranges defined as:
    John_Properties_Cost = (All_Properties_Owner=$B$115)*Cost_Of_Property
    John_Properties_Rent = (All_Properties_Owner=$B$115)*Rent_For_Property

    and series defined as:
    Series 1 X values = 'MyFile.xls'!John_Properties_Cost
    Series 1 Y values = 'MyFile.xls'!John_Properties_Rent

    I would do this:

    named range defined as:
    John_Properties_List = (All_Properties_Owner=$B$115)

    [this results in an array of 1s and 0s telling whether or not a property on the list belongs to john]

    and series defined as
    Series 1 X values = 'MyFile.xls'!John_Properties_List*F5:F40
    Series 1 Y values = 'MyFile.xls'!John_Properties_List*G5:G40

    where columns F and G hold the cost and rent data respectively.

    HOWEVER (here's where I get to the problem) I can't seem to get the syntax right, and have a bad feeling that it's because I'm not allowed to perform calculations within the series box - i.e. I'm trying to multiply one range by another and that's not allowed in the chart series definition. Excel tells me my formula contains an error whenever I try to enter one of these lines in the series box, and it always places the cursor at the *. I'd like to find some way to do this that has a reference to live cells in the workbook so that filtering the cells will still cause points to appear/disappear on the chart.

    And I want to do this all without using VBA, so that other folks using this file can edit these series without knowing VBA. And I'm running near to the column limit for excel, so adding an extra column per series (there are roughly 20 series) is something I'd really like to avoid. Can you confirm deny the "no multiplication in the chart series" hunch and suggest a better way (or find my error)?


  • Re: Filterable Dynamic Chart Range

    The no calculation in the chart series seems to be the case. But I am not 100% sure.

    You could however have the series refer to another named range that is the result of the calculation.
    See this page for clarification.


  • Re: Filterable Dynamic Chart Range

    Keiran, thanks for your suggestion. I think that having the formula refer to a named range that is the result of the calculation is exactly my problem. Because calculations done in the named range ignore filtering, they do not seem to meet my needs in this case. Do you know of a way to force a named range to return only the portion of its array that is visible (not hidden)?


  • Re: Filterable Dynamic Chart Range

    Just to clarify, a named range can be filtered just as any standard range so that only the visible cells show on a chart. But the filter must be an Excel filter. Strictly speaking, a defined name such as
    John_Properties_Cost = (All_Properties_Owner=$B$115)*Cost_Of_Property
    doesn't create a filter. It just returns a vector of zeros or property costs for the owner in B115. When fed to the chart, the chart just sees those values, not the underlying cells.

    You could perhaps achieve the desired chart by using an X-Y plot where the x scale minimuim is set to 0, and your vector of x values has, say, -1 for the property costs not associated with the designated owner. Not sure what you wpuld want for the other x-values though. Perhaps just a sequence of integers, based on a counting formula.

  • Re: Filterable Dynamic Chart Range

    Thanks, Derek. I understand that a named range such as this:
    John_Properties_Cost = (All_Properties_Owner=$B$115)*Cost_Of_Property
    will return a vector of zeros and property costs for the owner in B115. That's precisely the functionality I'm trying to use.

    However, if I use a direct cell reference to the property costs of the properties John owns as a chart series (i.e. a reference to all the individual cells/non-contiguous ranges that belong to John in the "cost" column), I get the additional functionality that if I us filters on the data, the chart updates to show only the data in rows that are visible. For instance, I can use filters to quickly show only a subset of monopoly properties - e.g. show only yellow properties, and the chart will update automatically by not showing the rows that are now hidden.

    If I use the named range approach above, filtering the table does not change the values in the named range, so my chart no longer automatically updates in response to filters. Any suggestions for how I can regain this functionality while using a named range?

  • Re: Filterable Dynamic Chart Range

    If you are working in Excel 2003, please also ensure that you have checked the Option "Plot visible cells only" under Tools-Options-Chart Tab.


  • Re: Filterable Dynamic Chart Range

    Although, I doubt that this could be the solution to your problem; as you have already got the desired result when you use direct cell references instead of named ranges.


  • Re: Filterable Dynamic Chart Range

    My point above was that there is no difference between directly referring to a range of cells and having a named range that directly refers to a set of cells as far as whether filtering will hide plotted points or not.

    A definition such as:
    John_Properties_Cost = (All_Properties_Owner=$B$115)*Cost_Of_Property
    does not return a range; hence the filtering doesn't work.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!