I can get line chart to ignore zeros but the legend still contains
I am using Excel 2007.
Thanks
"jaimeo" wrote:
Hi,
I have a table linked to a series of worksheets. The table shows products 1
to 6 in cells A2:A8. It then shows sales for each year in b2, c2. c3 etc.
for product 1. b3, c3 etc for product two and so on.
It is designed to show products sold in lots of different countries. I have
a line chart linked to the table. It works fine if all six products are sold
in the country. If they are not then because Product 1-6 is always entered
even if not all products are sold the legend still shows them all. I have
tried NA'ing the cells which stops them being charted but the legend entry
remains. I tried returning a blank cell if the values for the series were
all 0 but the legend entry was still there, just blank but still assigned a
colour. I even tried deleting the data out of the cells entirely so they
were genuinely totally blank but the legend still picks it up.
I only need it to ignore a product if every value in the series of figures
for turnover is 0 (i.e. we do not sell it in that country). There are 120
possible charts that can be produced from the table so I am hoping to
automate it. Originally I thought maybe I should use the conditional
formatting posts I have seen around to do it. However, when I pass this work
to someone else there is a good chance they may decide they want the graph
colour design to have a different colour background thereby highlighting what
conditional formatting would have hidden.
What I am leaning towards now is a way to effectively say "if all values in
this series are 0 then ignore it entirely" with the option then that if in
the future we launch that product the chart then recognises the values are no
longer zero, I can rerun the code or whatever and it will prepare me the
correct chart.
Apologies for the long winded response. I am very much a VBA beginner and
even an Excel beginner but I am learning a lot as I dig into this.
Please advise if you can.
|