Below is a video that may help you as well.
How-to make a Dynamic Excel chart that doesn’t show blank values so you don’t have to use filters.
It uses a combination of Index and Match functions along with an Offset function.
Here are other links to posts that involve Index/Match and Offset Function techniques to build Excel Charts:
Video Demonstration: http://youtu.be/JA2r_9RjLCs