Hello!
I'm completely new to this forum and my knowledge in excel is scarce. Searching for similar posts is hard and that's why I decided to make a new post.
So. I'm using Excel 2012 and have a large excel file. This file contains these kind of worksheets:
I have been following How to create an Interactive Chart in Excel? [Tutorial]. It worked out wonderful with one combo box (select an item). The problem is that I can't make it work with the second combo box (select a year).
The final result should be one chart with two combo boxes. When you alter the year in the 1st combo box, the 2nd combo box should adapt automatically to the new charts.
For example: I want to browse through the 2013 years data, so I select 2013 in the combo box. *Magic* Now I want to select the item "XL Toothbrush" and see it's data. Now I have a chart with the data for "XL Toothbrush" from the year 2013. Suddenly I realized this is the wrong year, so I select 2014 instead. *Magic* The chart automatically recognized the new selection and updated to show "XL Toothbrush"'s data from 2014.
There you have it! *Magic* corresponds to the problem I have. I can select items, but not another year.
So far I've tried to create a new worksheet with all interactive charts. Then I defined a name for each one of those charts. Lastly I created a chart in the final chart worksheet, linked to one of the charts found in the new worksheet. So the idea was that when I select an item, the charts in the new worksheet changes while the final chart only updates through the link. Selecting a year would just change so the final chart would point it's link to one of the other charts. Unsurprisingly, it didn't work out.
As I told you earlier: I'm inexperienced in excel and would be glad if someone could make a short step-by-step tutorial on how to make this work. Ultimately I believe it has something to do with name definitions and the CHOOSE formula.
Thanks for reading and hopefully helping.
I'm completely new to this forum and my knowledge in excel is scarce. Searching for similar posts is hard and that's why I decided to make a new post.
So. I'm using Excel 2012 and have a large excel file. This file contains these kind of worksheets:
- data of items (each worksheet corresponds to a country that has data for each sold item during a month for one or more years) [E.g. England, 150 items, item "XL Toothbrush" - 253x sold during January, 0x sold during February, etc., valid for 2014 and 2015]
- sum of data (one worksheet that has a list of all items [independent of countries] and the quantity of each sold item during a month for one or more years) [The same layout as above]
- charts (each worksheet represents a list of items sold during a specific year) [E.g. D2015, 150 charts, chart "XL Toothbrush" - sold items during January-December]
- final chart (one worksheet that shows only ONE chart and has two combo boxes: first one for a specific item, second one for a specific year)
- information (one worksheet that holds the information for the final diagram such as: item names and years shown in the combo boxes)
I have been following How to create an Interactive Chart in Excel? [Tutorial]. It worked out wonderful with one combo box (select an item). The problem is that I can't make it work with the second combo box (select a year).
The final result should be one chart with two combo boxes. When you alter the year in the 1st combo box, the 2nd combo box should adapt automatically to the new charts.
For example: I want to browse through the 2013 years data, so I select 2013 in the combo box. *Magic* Now I want to select the item "XL Toothbrush" and see it's data. Now I have a chart with the data for "XL Toothbrush" from the year 2013. Suddenly I realized this is the wrong year, so I select 2014 instead. *Magic* The chart automatically recognized the new selection and updated to show "XL Toothbrush"'s data from 2014.
There you have it! *Magic* corresponds to the problem I have. I can select items, but not another year.
So far I've tried to create a new worksheet with all interactive charts. Then I defined a name for each one of those charts. Lastly I created a chart in the final chart worksheet, linked to one of the charts found in the new worksheet. So the idea was that when I select an item, the charts in the new worksheet changes while the final chart only updates through the link. Selecting a year would just change so the final chart would point it's link to one of the other charts. Unsurprisingly, it didn't work out.
As I told you earlier: I'm inexperienced in excel and would be glad if someone could make a short step-by-step tutorial on how to make this work. Ultimately I believe it has something to do with name definitions and the CHOOSE formula.
Thanks for reading and hopefully helping.
