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?