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)?

Thanks!

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)?

Thanks

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?