Summary dashboard to display results for specific months

[Sreehari]

Hi Ninjas,

I want to create a dashboard which help me to display the results when I select specific months . This summary dashboard should give me the results based on a values available in different sheets.

I had attached one sample file with comments in each column on how to arrive the value in the summary sheet. Hope this gives you more clarity on my requirements.

request your help.

regards
Sreehari

 

[Awesome Wally]

Have you tried Index/Match to achieve desired results?http://chandoo.org/wp/2010/11/09/2way-lookup-formulas/

 

[vletm]

Sreehari
You should recheck and verify You sample results - something no match.
You would highlite or give exact ranges how You have got those results.

 

[Sreehari]

Veltm,

I now modified the comments in each column in the summary sheet for better clarity. kindly check now.

 

[Sreehari]

attachment.

 

[vletm]

Sreehari
Something like this?

 

[Sreehari]

Yes, exactly the same.Is this a Macro , or done in based on formula.Kindly provide the details of how this can be done also so that i can replicate it and use.

 

[vletm]

Sreehari - instructions:
1) Use 'my file'
2) Copy Your real data to System1- and System2-sheets as in 'my file'
Note: Layouts have to be same!
3) Select 'Month' from Summary-sheet's Cell D3
4) Check results

 

[Sreehari]

Vletm,

Thanks for the instructions, but i am more interested on the details of how this is created. because the file i had shared is just a sample file and the system1 and 2 are sample names . this is required for my office use and i may need to change the name and also needs to include more systems and sheets.

kindly help me with more details about the query used ( macro/VB.. or formulas etc.) so that i can modify and reuse it.

Please help me in learning this.

 

[vletm]

Sreehari
Do You know about VBA?
If so, then You can open that code and see how do it work - now.
Of course that 'my file' is also a sample.
Of course, it is possible to to modify ... many ways ...
but if You haven't use VBA ...
then there would be some 'challenges'
or 'not good way - just copy paste'.
It won't be short story 'just write' details what You should do ..
no matter number of many 'systems' nor 'months' do You have.
Layout should be same!
few details - which You can notice from code:
You have to solve ..
... selected months length (days).
... with which sheets (system) You're working.
... month's position from 'system-data'
... which (Your sample has two) way You collect Your needed results
It would be more quicker for You
if You could send more real sample informations.

 

[Sreehari]

Thanks ,

I am only a beginner in excel VBA, hence it will be a challenge for me to understand the script in full except for few lines.

to make my requirement more simple and user friendly i modified the data source sheets ( system1 & 2) . kindly help me with modifying the scripts accordingly .

note : opening value for Feb is equal to closing value in Jan and opening value for march is closing value for Feb , similarly for all up coming months.

 

[vletm]

Sreehari - oops!
You changed almost everything!
Now, it's possible to do without VBA.
You can see some samples ... not ready ...
because
are You sure that Your sample 'monthly' values are correct?

 

[Sreehari]

thanks , I had updated my comments in the attachment.

 

[vletm]

Sreehari
1) Do You mean that left (original) values are correct?

If so, explain Your 'Balance 1 Jan' value ... is fixed
'Balance 1 Feb' should be 'Balance 1 Jan' value+ changes of Jan (-2.069.455.000) as You would see above.
How Your 'Balance 2 Feb' values is greater than 'Balance 1 Jan' value?
2) Pivot 'Years & Date': Grouping You dates from B-column.
3) Balance figure: What is not matching?
[M3] = change of Jan, [M4] = change of Feb, [M5] = change of Mar.
4) System2: Where comes these values? ... from old version... hmm?
Do those has any connection with You sample data?
or manually calculated from other data?
Will You calculate those manually later or how?
[P4] looks same with 'sample datas' which can see from [AC4]
> Ideas ... Answers ... Questions?