Some dashboard questions.

[hawki]

Hi everyone!


I've been lurking this website for some time, reading about creating excel dashboards and VBA methods. I'm a student Business Informatics and am currently an intern at MVGM Vastgoed in the Netherlands. I've been given the task to create multiple excel dashboards from which management information can be easily derived. I've got a couple of questions and was hoping one of you awesome excel wizards could help me out.


I've read and watched the tutorial on making an interactive sales dashboard and tried to recreate it. Instead of using it for sales data I am using it to show wether or not suppliers reached one of their service goals. Currently there are "Goed" (good) and "Onvoldoende" (insufficient).


My first question:


How can I add 2 more columns in between "Goed" and "Onvoldoende", and which parts of the calculations tab do i need to adjust to make them fit in the dashboard? The ones i want to add are "Voldoende" (Sufficient) and "Matig" (Mediocre). I know i have to add these two to lstRegions, but whenever i do that and try to fill the data tab with these two i'm completely lost on where i should continue.


My second question:


I want to add a table in which the management of the company i'm working for can enter the dates on which the suppliers supplied their goods and i want to calculate the dispersal of these dates (the dispersal can be either Good if the dispersal is 0-20%, Sufficient if the dispersal is 0-30%, Mediocre if the dispersal is 0-35% or Insufficient if the dispersal is larger then 35%). For example, if a supplier supplied all of their goods in the same month, the dispersal is naturally insufficient. I have little to no clue on how i can add a table in which they can add the supply dates, after which a calculation is made on what the dispersion is. For example. Supplier A delivered goods on the 2nd of februari, the 6th of june, the 9th of october and the 10th of december. I want to add a table in which these dates can be entered, somekind of calculation is made (in which it calculates wether the dispersion is good, sufficient average or insufficient) and automaticly updates the data in the data table.


Here is the downloadlink to the file:


http://www.2shared.com/file/aHtnszXt/Spreiding_MVGM_versie_06.html


I really hope someone can help me out here, i'm lost beyond words!


Kind regards,


Jurgen van Delft

 

[hawki]

Anyone that can help me out here?

 

[Smallman]

Hi Hawki


I have had a look at your file. It is a Chandoo dashboard - a nice one at that - and you have picked one which will require you to do some work on a number of fronts. As the file currently sits you will need to add Sufficient and Mediocre (two new criteria) to the 1stRegion named range on the Data sheet. You will need to add two columns in the Berekeningen sheet starting in D8:D28 and add the two new criteria. You will need to add two columns to the Cols I8:I14. You will need to add all the calcs that go with the new criteria. You will have to update charts, named ranges along the way and finally you will have to alter the worksheet change event to cope with the new criteria you are adding.


If you know what you are doing it will take a couple of hours and I am not including any unforeseen problems which come while updating the file. I would say this is why your post has been given a wide birth. The size and scope of the work means you will have to nut this all out yourself (I may be wrong and there is a kind hearted sole with some time on their hands) but it will be a great learning experience for you. Everything you want to do is possible, it will just take some time.


Good luck and take care


Smallman

 

[hawki]

Thanks a bunch for your extensive post Smallman. Another question arose during the editing of the dashboard; what exactly do i alter in the worksheet change event?

 

[NARAYANK991]

Hi Jurgen ,


I can help out with what you want , but not on your worksheet , which is Dutch to me , but on the original file of Chandoo's.


You can then take whatever has been done in that file , and do the same with your file.


However , though this may be a matter of a 'couple of hours' , for me it will take probably a week ; if this is acceptable to you , let me know.


Narayan

 

[Smallman]

Woah


I was pretty sure it was going to take a some time to alter the file Hawki. What Narayan has offered you is a veritable gold mine. I would grab it with both hands. Very generous!!!


Take care


Smallman

 

[SirJB7]

Hi, hawki!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Some%20dashboard%20questions.%20-%20Spreiding%20MVGM%20versie%200.6%20%28for%20hawki%20at%20chandoo.org%29.xlsm


I did this:


a) Adjusted column J of worksheet Data to hold the 2 new values (VanKönig & VanMáxima, e.g.).


b) Changed the named ranged definition of lstRegions from:

=Data!$J$4:$J$5

to the dynamic named range definition:

=DESREF(Data!$J$4;;;CONTARA(Data!$J:$J)-1;1) -----> in english: =OFFSET(Data!$J$4,,,COUNTA(Data!$J:$J)-1,1)


c) Changed the named ranged definition of lstSpreiding from:

=Data!$J$4:$J$5

to:

=DESREF(lstRegions;;; -----> in english: =OFFSET(lstRegions,,,,)


d) Changed the named ranged definition of lstSpreiding from:

=Dashboard!$B$19:$B$21

to:

=DESREF(Dashboard!$B$19;;;FILAS(lstRegions)+1;1) -----> in english: =OFFSET(Dashboard!$B$19,,,ROWS(lstRegions)+1,1)


e) Inserted 2 columns in worksheet Berekeningen for rows range D6:D28, shifting to the right.


f) Copied C8 cell formula in worksheet Berekeningen thru F8.


g) Copied C9 cell formula in worksheet Berekeningen thru F14.


h) Replaced fixed strings in worksheet Berkeningen at range C17:F17 by this formula:

=INDICE(lstRegions;COLUMNA()-2;1) -----> in english: =INDEX(lstRegions,COLUMN()-2,1)


i) Copied C18 cell formula in worksheet Berekeningen thru F28.


j) Inserted 2 columns in worksheet Berekeningen for rows range K6:L17, shifting to the right.


k) Copied J18 cell formula in worksheet Berekeningen thru M8.


l) Added 2 data series in the chart of worksheet Dashboard to reflect the new added values.


m) Set the new data series to:

=Berekeningen!$K$8 & =Berekeningen!$K$9:$K$14

=Berekeningen!$L$8 & =Berekeningen!$L$9:$L$14


And I think that's all. Please check it and advise if any issue.


Regards!