Excel Dashboard

[Bobby80]

Hi Everyone,
I'm working on the new dashbaord. I have about 7 KPIs in the dasboard with the actions for a week separately for each centre. I have four different centres to choose. I want to show the 7 KPIs and actions under each individual centre name by selecting the the dropdown box at the top. The KPI's and actions will be differnet for each centre and wil be different each weekly..could you please help me in setting up with the dashboard.

Thanks in advance..

 

[Jon von der Heyden]

Hi Bobby80

It's a bit hard to suggest without knowing how you want to display the data. A sample would go a long way to help. That said perhaps you can draw something from this example.

Say sheet2 houses a list of your KPI's and each is assigned to a centre. First we create a list of distinct centres so that you can use this for a validation list:

Excel Workbook
	A	B	C	D
1	Centre	KPI		rng_DistinctCentres
2	Centre1	C1KPI1		Centre1
3	Centre1	C1KPI2		Centre2
4	Centre1	C1KPI3		Centre3
5	Centre1	C1KPI4		Centre4
6	Centre1	C1KPI5		#NUM!
7	Centre1	C1KPI6		#NUM!
8	Centre1	C1KPI7		#NUM!
9	Centre2	C2KPI1		#NUM!
10	Centre2	C2KPI2		#NUM!
11	Centre2	C2KPI3		#NUM!
12	Centre2	C2KPI4		#NUM!
13	Centre2	C2KPI5		#NUM!
14	Centre2	C2KPI6		#NUM!
15	Centre2	C2KPI7		#NUM!
16	Centre3	C3KPI1		#NUM!
17	Centre3	C3KPI2		#NUM!
18	Centre3	C3KPI3		#NUM!
19	Centre3	C3KPI4		#NUM!
20	Centre3	C3KPI5		#NUM!
21	Centre3	C3KPI6		#NUM!
22	Centre3	C3KPI7		#NUM!
23	Centre4	C4KPI1		#NUM!
24	Centre4	C4KPI2		#NUM!
25	Centre4	C4KPI3		#NUM!
26	Centre4	C4KPI4		#NUM!
27	Centre4	C4KPI5		#NUM!
28	Centre4	C4KPI6		#NUM!
29	Centre4	C4KPI7		#NUM!
Sheet2

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Formula in D2 copied through to D29. Then create a new name, called 'rng_DistinctCentres'. Refers to:
=INDEX(Sheet2!$D:$D,2,0):INDEX(Sheet2!$D:$D,MATCH(REPT("z",255),Sheet2!$D:$D,1))

Now, assume you want to have a validation cell in Sheet1 that allows the user to choose a centre of particular interest. When the user makes his/her choice, each of the relevant KPI's will be listed in B5:B11. In this example B2 houses a list validation that refers to 'rng_DistinctCentres'.

Excel Workbook
	A	B
2	Centre	Centre1
3
4
5	1.	C1KPI1
6	2.	C1KPI2
7	3.	C1KPI3
8	4.	C1KPI4
9	5.	C1KPI5
10	6.	C1KPI6
11	7.	C1KPI7
Sheet1

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Formula in B5 is copied down through to B11.