Add multiple series to a dynamic chart

Question

I have a large amount of data stored in a table that is used to update about 8 charts (through named ranges). Each chart has the date and ID as the x-axis with a particular parameter being charted. Each column of the table has its own named range which allows this to work and for the charts to update as more data is added.
This all works great, but now the end user is requesting that one of the series on the charts is broken down into some other series. For example my data could be:

Date      ID       Source     Parameter 1      Parameter 2      Parameter 3
7/21/15   11       Boston       12               55               5.25
7/21/15   12       New York     10               53               5.05
7/21/15   13       Boston       11.2             55               5.16
7/21/15   14       New York     12               57               4.95
7/21/15   15       New York     10               56               5.62
7/21/15   16       Albany       15               54               5.80
7/22/15   11       Albany       17               56               5.62
7/21/15   12       Vermont      19               58               5.34

Currently, all this data is being charted as one series, but now I would like to have different series for each of the sources. Since there is so much data, I would really rather not have to sort it and manually edit the source data for each of the charts as this would be very time consuming. Does anyone have any solutions on how to separate the named range based on the value in a particular column?

To add a visual of the chart I am trying to edit:

The table above is a simplified version of the data. The charts themselves include data from multiple worksheets, but the series I would like to add would be a breakdown of the Cities series shown above.

Any and all help is appreciated.

Answer 1

To break out your series based on a value in your source (or any other column), you could use helper columns.

1. Create an additional column for each value you want to break out.
2. For your Boston column, use the formula =IF([Source] = "Boston", [Parameter 1],na()).
3. Repeat for each additional source, replacing the comparison value as appropriate.
4. Define the helper columns as named ranges.
5. Chart those newly created ranges. The n/a values won't chart, so you'll be left with the appropriate parameter values for each chart/series combination.