Dear All,
I use Excel 2003, english version.
I have an excel file, containing a main sheet with all data and several sheets (one per product) which are fed (linked to) the main sheet.
On each of the product specific sheets, I have a table showing Categories in Row A and the belonging percentages in Row B. Below, I created a Pie Chart showing this data. The Pie Chart is linked to a Word File, so that when I open the word file, it updates the Chart (automatically or manually).
Obviously, I want the pie chart to not show the zero % values. However, I have some troubles with this.
I already tried the following:
- AutoFilter: combined with a sheet code I found in one of the forums (Worksheet Calculate), the AutoFilter updated automatically so whenever I changed data in the main sheet, the tables and the charts on the product-specific sheets were always updated and only showing values with more than 0%. Problem: When I click on "update link" in the word file, the auto filter disappears in the excel sheet and the pie chart starts showing all values again, also the zero % ones.
- AdvancedFilter: Works fine, but I can't make it update automatically. So when I change something in the main sheet, the table & pie chart in the other sheets do not change. I do not want to have to activate the filter manually on each single sheet.
Is there another way of doing this?
Many thanks
Sandra
PS: I am not an excel expert, so I am not really familiar with macros etc.
I use Excel 2003, english version.
I have an excel file, containing a main sheet with all data and several sheets (one per product) which are fed (linked to) the main sheet.
On each of the product specific sheets, I have a table showing Categories in Row A and the belonging percentages in Row B. Below, I created a Pie Chart showing this data. The Pie Chart is linked to a Word File, so that when I open the word file, it updates the Chart (automatically or manually).
Obviously, I want the pie chart to not show the zero % values. However, I have some troubles with this.
I already tried the following:
- AutoFilter: combined with a sheet code I found in one of the forums (Worksheet Calculate), the AutoFilter updated automatically so whenever I changed data in the main sheet, the tables and the charts on the product-specific sheets were always updated and only showing values with more than 0%. Problem: When I click on "update link" in the word file, the auto filter disappears in the excel sheet and the pie chart starts showing all values again, also the zero % ones.
- AdvancedFilter: Works fine, but I can't make it update automatically. So when I change something in the main sheet, the table & pie chart in the other sheets do not change. I do not want to have to activate the filter manually on each single sheet.
Is there another way of doing this?
Many thanks
Sandra
PS: I am not an excel expert, so I am not really familiar with macros etc.
