Adding a categorical series that is dependent on an already existing series

[spidroin]

I am trying to create a clustered bar chart. The x axis is service area (zip code) and the y axis is percentage of patients. I have two bars for each zip code corresponding to a source facility and a receiving facility. I would also like to demonstrate if the zip code is part of the facility's primary or secondary service area. In my table, "1" is primary and "0" is secondary. I want to create another series, but I don't want to create another bar. I'd just like the already existing bars to have a solid fill if primary and pattern fill if secondary.

I know I can just do this manually, but I will be creating a tool for others to use so I'm trying to find a way for the chart to automatically generate when the data is entered. Additionally, for the attached data, the primary and secondary service areas are identical for both facilities; however, not all cases are like this.

Any feedback or additional resources are appreciated! Thank you

[Andy Pope]

Add 2 more series and move both to secondary axis.

Format original columns to be shaded.
The 2 new series will have solid fill.
Delete secondary vertical axis.
Remove 1st for series from legend, unless you want the shaded legend entries displayed.

The data for the 2 new series will be based on formula that only display data if the site is a primary one.