Prev: Third Axis - alignment of number instead of percent
Next: How do I use an indirect reference for a chart scale in Excel?
From: jaimeo on 5 Mar 2010 15:54
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.
From: jaimeo on 5 Mar 2010 16:04
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.
From: AdamV on 12 Mar 2010 09:22
Can you not just use an autofilter to filter out the rows which are
blank? (based on an extra column which checks all the previous columns,
or a simple SUM of them perhaps).

If you do want to automate it based on something changing, or giving the
user a button for this, then the one line macro below should do the
trick - this filters the fourth column of your table, adjust the 4
according to your needs.


ActiveSheet.Range("$C$7:$F$13").AutoFilter Field:=4, Criteria1:="<>0",
Operator:=xlAnd


If your data can be brought across in a suitable layout then a
PivotChart might be a better option to ignore data that does not occur
in the source.

Hope this helps.
Adam

On 05/03/2010 21:04, jaimeo wrote:
> 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.
 | 
Pages: 1
Prev: Third Axis - alignment of number instead of percent
Next: How do I use an indirect reference for a chart scale in Excel?