Change chart according to two drop down lists with data from multiple sheets

0
votes

Hi i would like to create a dashboard where my charts will change when I filter them by country and months according to their respective drop down lists.

For example: Sorted by 'Australia' and 'Aug'

enter image description here

My charts are linked to the table as shown. For example, one of the data source is O60:P62.

enter image description here

The problem:
For each country, I have different sheet tabs with similar data formats with different values. I would like to know how can I use one master drop down list in my 'Dashboard' sheet and link it to each data source in each sheet when I choose a specific country from the drop down list.

Adding on, the second drop down list for months will follow according for the country selected from the first drop down list. The end result would be my dashboard charts changing according to the country and month selected.

enter image description here

I hope my explanation is clear. Thanks in advance!

1
excelvbaexcel-2016
Sounds to me like you want pivot charts with slicers. Edit didn't read it all. I see your problem with the sheets. Why do you have it on different sheets? – Andreas
@Andreas Hi, I have sorted the countries by each sheet as they have different values, while using the same data format as shown in the second image. – Lim Hong En

1 Answers

0
votes

For the country, you you have to fill the data on the Dashboard worksheet with the INDIRECT worksheet function to get the data from the worksheet of the selected country.

Just as an example, let's assume the formula in B61 is ='Australia'!B5, then you have to replace this with =INDIRECT("'"&$A$1&"'!"&CELL("address",B5)).
If you then change the contents of A1 either by typing directly or through the drop-down box (e.g. replace Australia with Malaysia), the formula will get the data from the Malaysia worksheet.

And for the month, you can find the data with a simple INDEX(MATCH) combination, or with a HLOOKUP. Instead of typing "Aug" in P60 and P64, you put =$B$1, so it takes the value from the drop down box.