Charting a Changing Data Range

I am attempting to create a material price variance report for work.

As part of this I would like to have a line chart for each buyer, which shows the variances relating to each supplier for every month. I have set up my spreadsheet, so that I can select the buyer name from a drop down list and this in turn updates various charts and data tables.

For this chart in particular I have to download information from our accounting system and then run it through a pivottable. As the months go by more and more suppliers may cause variances, but no buyer has the same number of suppliers (some have 5 suppliers, but some may have as many as 20).

Whilst I can make a spreadsheet only pick out the information required from the pivottable to plot, it involves having some error values when a buyer only has a small number of suppliers, i.e. I make the formala show NA() or "" if there is no supplier to pick up.

How can I make the chart not pick up these non suppliers in a legend/data table on the chart? It doesn't plot the points (as there is no data) but I don't want to see NA# in the legend for all the rows that don't return a supplier.

Any help would be greatly appreciated.

I am not coding this, simply using charts on a set range.

I intend to send the file out to the buyers, who will be able to select their name from a drop down box. Therefore I'd like to avoid the need for them to hide rows etc and have it done automatically.

Its just a shame that whilst NA() can be used to stop a graph plotting points, there isn't something similar to stop creating a series on a chart.