Excel Dashboard Help!

[Gaurang Mhatre]

Hi All,
Need your help!

Attached is the dummy dashboard which I have made. There are 4 sheets in workbook.

1st sheet- Snapshot (Linked with MOM Dashboard and YTD sheet for data)
2nd Sheet- MOM Dashboard (Data linked with Snapshot)
3rd Sheet- YTD (Data linked with Snapshot)
(*Linked means I have used formula with help of those sheet)

In snapshot sheet, there are 3 categories.
1) Current month snapshot (Select a month and data will come as per that month)
2) Monthly Snapshot
3) YTD Snapshot

These are my basic requirements for showing a dashboard.

What I did in snapshot sheet is that, I have put formulas for 1st category i.e. Current month snapshot.
I have used Countifs formula and I have collected that info from 3rd sheet i.e. (MOM Dashboard) So whenever I select a particular month the formula will capture the details accordingly.

But, if you see the 2nd category does not change even if there is formula in it nor the 3rd category changes. What I want is, to change 2nd and 3rd category simultaneously as soon as I click on a month.

For example, If I choose Jan’14 month then 1st category will show Jan’14 data and the 2nd category will show data till jan’14 and so does the YTD will capture data from Apr’13 to Jan’14 only. Which does not happen in the formula I have used since I don’t know the advance level of it. All the formulas should be captured as per the “Actual live date”.

If you compare 3rd category in SNAPSHOT sheet and YTD consolidated sheet, Data is captured with help of Column U (Apr-Dec) since I don’t know the advance formula to capture specific months ( what I mean is, If I click on month Jan’14 in snapshot sheet, then YTD (3rd category in snapshot sheet) should capture data from Apr’13 till Jan’14 data and if I click on Oct’13month in Snapshot sheet, then YTD should give data from Apr’13 to Oct’13) For this I guess an advance formula is required which I’m unaware) thus, I have inserted an extra column and capture the info with the help of countifs formula which is an easier step as I need only the implemented mandates info. In short, If you observe the formula which I have used you will clearly understand what I want to capture.

Only thing is I need is a formula which will capture the 2nd category and 3rd category automatically. In 2nd category, If I select a month Oct’13 then the formula should capture from Apr’13 to Oct’13 month as shown in below figure. Nov till Mar’14 data it should not capture as I have chosen Month Oct’13, So till oct’13 data should reflect and bar graph should come accordingly



As I said earlier, YTD Category should reflect month chosen above and graph should come accordingly.



So guys can you please help me in this coz current situation captures as per basic formula but I want to make it more innovative by a simple click, so that same method I can apply for new quarter.

It would be great if you could help me in this.


Thanks & Regards,
Gaurang Mhatre

 

[NARAYANK991]

Hi Mhatre ,

No doubt others will reply to your specific questions ; I would like to say something fundamental.

The most important point about an interactive dashboard is the responsiveness ; a dashboard which is slow to respond will seldom be appreciated.

When you create a workbook , before saving it , check out the extent of each worksheet by pressing CTRL END ; this will take the cursor to where Excel thinks the last used cell is ; in your Snapshot tab , this is at AS1048576 !

Please look into this , and do what is required. To reset this to the correct values , go through these links :

http://support.microsoft.com/kb/244435

http://www.accountingweb.com/article/resetting-last-cell-excel-worksheet/221618

http://dmcritchie.mvps.org/excel/lastcell.htm

Narayan

 

[Somendra Misra]

Hi Gaurang,

In addition to what Narayan sir added, I would like to add that months in your database are text strings, which is not a good practice as dates can be used for various types of calculation on Dashboards.

So you can type 1/4/2013 in cell and format the cell to mmm'yy so that the yser can see the months in desired format.

Now coming to your questions, this can be solved by putting a formula of the patter >= & <= but since you have text as Months, this will become difficult to achieve.

Give a thought over it.

Regards,

 

[Gaurang Mhatre]

Thanks for your reply Narayan and soumendra. Will check on size peice. But still clueless about >=&<= thing.

Anyways Thanks once again!

Regards,
Gaurang.

 

[Somendra Misra]

@Gaurang Mhatre

Kindly, go through my comment in details. You will get the answer.

Regards,

 

[Gaurang Mhatre]

Hi Friends,

Attached is the dummy dashboard which I have prepared. Just wanted to know how can I hide those table and show only graph. But want table for reference. Just Want to make it interactive.

Can you guys share some tips.


Thanks!

Regards,GAURANG