I need help with a Customer Service KPI Dashboard

[Nick24]

Hi everyone,

I'm really struggling with how to show some information on excel.

I'm trying to create a very simple dashboard that shows a pie chart for incoming calls received by each customer service employee. And another for outgoing and then a total.
I would like a bar chart next to it that relates to the pie chart showing.
The problem is that I would like a drop down box that seletea which chart to show, e.g incoming, outgoing & total.

Ive managed to play with data validation and make a drop down box, butdonr know how to link this to the relevant pie chart and bar graph.

I've also learnt to use named ranges to name each coloum.

The information is currently in 5, rows. With the titles: executive, incoming calls, outgoing calls, total calls. Then the 7 employees data and a final total row.

I would appreciate any help as I'm trying to impress mangement and eventually create great dashboards. I'm a very fast learner.

Many thanks

Nick

 

[SteveO59L]

Use vlookup() with your data validation cell to reference the data from your table

 

[Nick24]

Hi Steve,

Thank for the reply,

Could you possibly elaborate a little, im only used to using vlookup() to get one value, e.g one of the customer service teams outgoing calls, not all of them for the chart to use,

Thanks

 

[SteveO59L]

send me a pm with your email address, and I will send you a sample file

 

[Nick24]

Okay so i managed to get it working, But only if i split the data up on another tab so that it was specific to the executive. As i couldn't vlookup() both the name and type of call into one box. example for one below, the rest were listen below with a space in between.

[TABLE="width: 248"]
<tbody>[TR]
[TD]Executive[/TD]
[TD]bob[/TD]
[/TR]
[TR]
[TD]Incoming Calls[/TD]
[TD]397[/TD]
[/TR]
[TR]
[TD]Outgoing Calls[/TD]
[TD]140[/TD]
[/TR]
[TR]
[TD]Total Calls[/TD]
[TD]537[/TD]
[/TR]
</tbody>[/TABLE]

etc

This way i could just do =VLOOKUP(C6,Sheet1!A12:B15,2,FALSE) next to each of their names and below the data validation cell, (C6 being the data validation cell and the Sheet1!A12:B15 being each specific table for the executives).

Is there anyway i could use the Data validation cell to bring up a named range or something so i don't have to split all the information into each persons stats?

 

[Nick24]

Okay ignore the above i just pasted the table horizontally and it worked fine, (above but all the name next to each other with below data) Is there any way to do this vertically?