Automatically Updating Chart When Series Data is in Noncontiguous Cells

[Eshleman]

I have one graph for labor (A19), one graph for benefits (A20), one graph for supplies (A21), etc., all the way through Total (A29). Each graph is a line graph with markers and is on the same tab as the data.

My x-axis is fiscal years. My y-axis is unit cost. The x-axis is always C18, E18, G18 and currently goes through S18. My y-axis for labor is in cells C19, E19, G19…S19. My y-axis for benefits is in cells C20, E20, G20…S20. My y-axis for supplies is in cells C21, E21, G21…S21, etc.

My x-axis and y-axis series data are in noncontiguous cells because the unit cost is calculated with dollars (and cost drivers) that are also reported on the tab.

The data looks like this:

[TABLE="width: 501"]
<tbody>[TR]
[TD]Cost Driver
[/TD]
[TD="align: right"]20,000
[/TD]
[TD][/TD]
[TD="align: right"]24,000
[/TD]
[TD][/TD]
[TD="align: right"]28,000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] FY10
[/TD]
[TD] FY10
[/TD]
[TD] FY11
[/TD]
[TD] FY11
[/TD]
[TD] FY12
[/TD]
[TD] FY12
[/TD]
[/TR]
[TR]
[TD]Labor
[/TD]
[TD="align: right"]$200,000
[/TD]
[TD="align: right"] $10.00
[/TD]
[TD="align: right"]$325,000
[/TD]
[TD="align: right"]$13.54
[/TD]
[TD="align: right"]$290,000
[/TD]
[TD="align: right"]$10.36
[/TD]
[/TR]
[TR]
[TD]Benefits
[/TD]
[TD="align: right"]$100,000
[/TD]
[TD="align: right"]$5.00
[/TD]
[TD="align: right"]$145,000
[/TD]
[TD="align: right"]$6.04
[/TD]
[TD="align: right"]$158,000
[/TD]
[TD="align: right"]$5.64
[/TD]
[/TR]
[TR]
[TD]Supplies
[/TD]
[TD="align: right"]$115,000
[/TD]
[TD="align: right"]$5.75
[/TD]
[TD="align: right"]$138,000
[/TD]
[TD="align: right"]$5.75
[/TD]
[TD="align: right"]$156,000
[/TD]
[TD="align: right"]$5.57
[/TD]
[/TR]
[TR]
[TD]Other Service
[/TD]
[TD="align: right"]$22,000
[/TD]
[TD="align: right"]$1.10
[/TD]
[TD="align: right"]$20,000
[/TD]
[TD="align: right"]$0.83
[/TD]
[TD="align: right"]$19,000
[/TD]
[TD="align: right"]$0.68
[/TD]
[/TR]
[TR]
[TD]Travel
[/TD]
[TD="align: right"]$100
[/TD]
[TD="align: right"]$0.01
[/TD]
[TD="align: right"]$0
[/TD]
[TD="align: right"]$0.00
[/TD]
[TD="align: right"]$0
[/TD]
[TD="align: right"]$0.00
[/TD]
[/TR]
[TR]
[TD]Utilities
[/TD]
[TD="align: right"]$400,000
[/TD]
[TD="align: right"]$20.00
[/TD]
[TD="align: right"]$300,000
[/TD]
[TD="align: right"]$12.50
[/TD]
[TD="align: right"]$400,000
[/TD]
[TD="align: right"]$14.29
[/TD]
[/TR]
[TR]
[TD]Maintenance
[/TD]
[TD="align: right"]$150,000
[/TD]
[TD="align: right"]$7.50
[/TD]
[TD="align: right"]$175,000
[/TD]
[TD="align: right"]$7.29
[/TD]
[TD="align: right"]$140,000
[/TD]
[TD="align: right"]$5.00
[/TD]
[/TR]
[TR]
[TD]Other
[/TD]
[TD="align: right"]$2,000
[/TD]
[TD="align: right"]$0.10
[/TD]
[TD="align: right"]$1,000
[/TD]
[TD="align: right"]$0.04
[/TD]
[TD="align: right"]$1,000
[/TD]
[TD="align: right"]$0.04
[/TD]
[/TR]
[TR]
[TD]Depreciation
[/TD]
[TD="align: right"]$50,000
[/TD]
[TD="align: right"]$2.50
[/TD]
[TD="align: right"]$40,000
[/TD]
[TD="align: right"]$1.67
[/TD]
[TD="align: right"]$65,000
[/TD]
[TD="align: right"]$2.32
[/TD]
[/TR]
[TR]
[TD]Plant Period
[/TD]
[TD="align: right"]$20,000
[/TD]
[TD="align: right"]$1.00
[/TD]
[TD="align: right"]$70,000
[/TD]
[TD="align: right"]$2.92
[/TD]
[TD="align: right"]$30,000
[/TD]
[TD="align: right"]$1.07
[/TD]
[/TR]
[TR]
[TD]Total
[/TD]
[TD="align: right"]$1,059,100
[/TD]
[TD="align: right"]$52.96
[/TD]
[TD="align: right"]$1,214,000
[/TD]
[TD="align: right"]$50.58
[/TD]
[TD="align: right"]$1,259,000
[/TD]
[TD="align: right"]$44.96
[/TD]
[/TR]
</tbody>[/TABLE]



What I am trying to do is automatically update each graph once the data is populated for the fiscal year that appears in the rightmost column (at the time of writing, fiscal year 2017 plan appears in column S).

In addition, I have one tab for every department (all derived from information consistent with what I describe above). Therefore, each department has its own set of graphs. My second goal is to update similar tabs with dynamic graphs using a macro or some similar device so that I don’t have to modify each data series on every department tab individually (I’m thinking that I would run it for only the active tab). I have to be careful here, though, because not all tabs are department tabs. Some tabs are summary tabs. While these summary tabs also contain graphs, they are created differently than the department graphs and do not pose the same type of challenges.

Any advice on how to update these department graphs in a productive manner would be greatly appreciated.