Rolling Month data in Dashboard

[Thomas Kuriakose]

Respected Sirs,

I prepared a dashboard using one of the Chandoo Dasboard examples and input our data.

Thank you for the amazing dashboard samples in the forum.

We need one more feature with a control for rolling months. This is a combo box with 12 months and when we select 2 from the combo box we need the consolidated data for 2 months to be displayed in the linked cells and charts.

Kindly let me know how to get this done.

Kindly find attached the dashboard for your reference.

Kindly also suggest if this can be improved.

thank you very much,

with regards,
thomas

 

[vletm]

@Thomas Kuriakose
Something like this ... Choose Month
... and about 'Chart Titles',
why You don't use those ... for 'Chart Titles'?

 

[Thomas Kuriakose]

Respected Sir,

The Month data is working already and giving results monthly,

what I need is if we select 2 in the no. of rolling months, it should add up values of Jan and Feb and represent in the cells and in the charts. If we select 3 in the rolling months it should add values of Jan, Feb and March and represent in the linked cells and the charts. and so on.

Thank you so much,

with regards,
thomas

 

[vletm]

@Thomas Kuriakose
That second dropdown hiding many times ...
but You can continue from this and make Your final settings for it...

 

[Thomas Kuriakose]

Respected Sir,

Thank you for this input provided.

The values are not getting added up. It is moving the months and data but the values are not getting added to get the YTD values.

I attached the data for Chart 5 results on selection of Feb month and no of rolling months 3 for your reference.

Thank you so much,

with regards,
thomas

 

[vletm]

@Thomas Kuriakose
In Your original version 'Choose Month' and 'No. of Rolling Months' had same 'cell link'. I understood that You need to see more Month-bars in that chart.
I did that. You can select from 1 to 12 bars to see.
Values to show were between 18 to 60 and 1 to 17.
Now, You suddenly want somewhere values between 6465 to 37538 and 0 to 16639.
Interesting, but what would be connection with those?

 

[Thomas Kuriakose]

Respected Sirs,

I tried to work on the rolling months data and managed to get one output, but i am not able to co-relate with the month data.

Kindly find attached the file where the rolling months data is working, but on selection of the month, the data is not getting offset.

Kindly check and let me know whether the correlation with month selection and no. of rolling months can be done.

Thank you very much,

with regards,
thomas

 

[vletm]

@Thomas Kuriakose
? <= this is a question mark. So, no connection!
Many things can do many ways ..
You could check one possible way ... Range(G40:AF43)
It uses same controls and also those links to A2 & A4.

 

[Thomas Kuriakose]

Respected Sir,

Thank you for the solution provided through VBA.

The L1 values are not correct.

The R1 values are correct.

Kindly check.

thanks,

with regards,
thomas

 

[vletm]

@Thomas Kuriakose
What 'The L1 values'? those fixed L1 values are Yours! or 'the result'?
If 'not correct' then You would tell me what would be the correct value for something and from which values is would calculate.
Name at least cells, if You would like to get something new.
Not only 'not correct'!

 

[Thomas Kuriakose]

Respected Sir,

Sorry for not having marked out the details for your reference.

Please find attached the required values:

Thank you very much,

with regards,
thomas

 

[vletm]

@Thomas Kuriakose
You could copy 'missing' code part one line above?
Almost like in Your 'correct answer'!

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the solution provided.

Thanks once again,

with regards,
thomas

 

[Thomas Kuriakose]

Respected Sir,

On question, when I select Month as Feb and number of rolling months as 12, it is giving me #Ref! in cell H40.

Kindly check and let me know how to correct this.

Thank you very much,

with regards,
thomas

 

[vletm]

@Thomas Kuriakose
modify Cell H40 formula's T41 to AF41