Good day,
I have a question with regards to dynamic tables and charts.
I set up three columns for five food types using an ActiveX combobox so that I can switch between different food types. This was done for 12 months for 5 products (per food type).
The first column is Product and has 60 rows. The second column is Sales volume and also has 60 rows and the third column is Month and also has 60 rows populated with data.
So, I want to create a line chart displaying the Months on the x-axis with a line for each product and Sales volume on the y-axis.
The problem is, when I set up the y-axis (vertical), I am extracting data from the Sales volume column which has 60 rows populated with data. This is populated perfectly in the chart.
I created two helper columns. One for the Products (since the products each repeat multiple times, I had to extract only the five products that I needed - which updates dynamically) and one for the Months (as there are 60 columns populated with month data, the months repeat multiple times).
When I however extract data from the Month helper column, Excel gets confused because it is now only extracting data from 12 columns (12 months) instead of the 60 columns for the Sales volume (y-axis).
So, when I try to populate the Months on the x-axis, Excel sees the 48 blank cells as data and I have tried just about everything to get it to ignore those columns, but nothing is working. I also can't manually delete the blank columns from the horizontal axis data input for the line graph.
So, the months are squashed in so tightly that they are written over each other on the x-axis.
How do I fix this error? And also, how do I populate a line for each product for each food type in the dynamic chart? I am struggling with that as well.
I would really appreciate your help.
Kind regards,
Heinrich
