Create chart on Home Page using data from Different sheet

Hi All,

I have a workbook that has two sheets currently in use. Tab "India", "Monthly_spends". Now I wish to have a scroll bar chart on home page based on country selected from the data validation list. The chart should show 3 months at a time, for e.g for jan, feb, mar...then apr, may, jun....then jul, aug,sep...then oct, nov, dec.

The chart should be selected for the country selected from the list. Currently data is for Region LATAM for the countries Mexico and Brazil. Help shall be highly appreciated.Please find the attached sheet for reference.

Thanks in advance!

Hello
I'm assuming the chart will be based on the Monthly_Spends tab. Take a look at the attached example. Maybe this is something you can work with.

Spot on DBY! I would further appreciate if you can just explain the process as I am new to the charts, that would further enlighten my skill. I know that would be a tedious job, but trust me my entire team would benefit as I believe in sharing knowledge.

Thank you in advance!

Driving the chart are two dynamic named ranges. The first one ‘DatesbyCountry’ with this formula:

Formula:

=OFFSET(Monthly_spends!$A$1,MATCH(India!$B$7,Monthly_spends!$A$2:$A$25,0)+Monthly_spends!$E$2*3,1,3)

The cell Monthly_spends!$A$1 is offset by the match function finding the match of B7 on the ‘India’ tab plus the Scroll Bar value on ‘Monthly_spends’. The Scroll Bar has a minimum value of 0 and max of 3 and increments by 1. The Offset formula multiplies this by 3 to move to each quarter.

This named range is added to the chart X axis values and the Amounts are derived from another named range ‘CountryAmounts’ which comes from Offsetting the ‘DatesbyCountry’ by 1 column.

Formula:

=OFFSET(DatesbyCountry,0,1)

The Chart Title is directly linked to B7 on the ‘India’ tab. The Quarter’s label in the top left corner of the chart is linked to the ‘Label Link’ on the ‘Monthly-spends’. That in turn is from the Lookup Table indexed from the Scroll Link.

I hope this makes some sense.
DBY

That is really lovely explaination DBY!

Thank you so much for your pain!