William Hulme
New Member
Hi All,
Please can I ask for some assistance with lookup formulas? I can do basic vlookup but that's it and I think I need more than that for this task.
I want to create a spreadsheet that will act as a dashboard to present different level data to different readers but from the same source.
It's for a call centre so in terms of hierarchy I have at the top Operations Managers, underneath these I have Team Managers and underneath these I have Advisors.
For raw data I expect to receive a spreadsheet that will detail the Operation Manager names with the Team Managers names who report in to them in another column, then the Advisors who report in to the Team Managers and then the metrics being measured being AHT (average handling time), ACW (after call work), NPS Advisor (a measure of customer service by that Advisor) and NPS Brand (a measure of how people rate the company based on their interaction with the Advisor).
So to start with I expect to receive a table of data with the headings - Operations Manager, Team Manager, Advisor, AHT, ACW, NPS Advisor, NPS Brand... or something along these lines with a series of numbers underneath. The data could be presented slightly differently so column and row numbers could change.
From this point I am looking to have a number of tabs, namely, OM Dashboard, TM Dashboard and Data.
Where I would like this to go is that the raw data is provided daily from a central source and I have a templated spreadsheet for the dashboards whereby:-
I get the data from the central source an paste it in to a tab entitled data. The format of the data will not change so all column and row titles etc will remain the same. The Advisors may change team from time to time or and the number of Advisors on a team may vary but any lookup function will capture them under their new manager when a change occurs.
On the tab entitled TM Dashboard, the Team Managers select their name from a dropdown box and all the Advisors reporting to that manager are then listed in a table which looks up the corresponding values for AHT, ACW etc. This data is then displayed on a graph for each metric.
The manager will see a few graphs populated for their team members, for example,the first graph will be for AHT and will show all Advisors with their call handling time so comparisons can be made when comparing like with like across Advisors.
The next graph down will show their ACW so it can be compared among the team.
The next graph will compare the Advisor NPS and the final graph will show the Brand NPS.
At this point the job is done for the Team Managers.
The Operations Manager will select their name from a drop down list and this will provide the names of the Team Managers reporting in to them. This way, comparisons of AHT, ACW etc can be made at a team level.
Having pondered this for a while I think that I may need a tab for each Team Manager so that when the data is pasted in, each will manager's tab will auto-populate having a lookup function to pull their own team's data for each of the metrics. When they use the drop down box it pulls the information forward to the dashboard tab and generates the graphs.
When the Operations Managers select their name from the drop down box, it would pull the totals for each Team Manager, again so comparisons can be made.
I suspect that index and match are the functions needed but I do not know enough about these at the present time to move this project forward.
I've attached the start of a spreadsheet of what I believe this may look like to see if this helps.
Many thanks of any thoughts and guidance.
Will
Please can I ask for some assistance with lookup formulas? I can do basic vlookup but that's it and I think I need more than that for this task.
I want to create a spreadsheet that will act as a dashboard to present different level data to different readers but from the same source.
It's for a call centre so in terms of hierarchy I have at the top Operations Managers, underneath these I have Team Managers and underneath these I have Advisors.
For raw data I expect to receive a spreadsheet that will detail the Operation Manager names with the Team Managers names who report in to them in another column, then the Advisors who report in to the Team Managers and then the metrics being measured being AHT (average handling time), ACW (after call work), NPS Advisor (a measure of customer service by that Advisor) and NPS Brand (a measure of how people rate the company based on their interaction with the Advisor).
So to start with I expect to receive a table of data with the headings - Operations Manager, Team Manager, Advisor, AHT, ACW, NPS Advisor, NPS Brand... or something along these lines with a series of numbers underneath. The data could be presented slightly differently so column and row numbers could change.
From this point I am looking to have a number of tabs, namely, OM Dashboard, TM Dashboard and Data.
Where I would like this to go is that the raw data is provided daily from a central source and I have a templated spreadsheet for the dashboards whereby:-
I get the data from the central source an paste it in to a tab entitled data. The format of the data will not change so all column and row titles etc will remain the same. The Advisors may change team from time to time or and the number of Advisors on a team may vary but any lookup function will capture them under their new manager when a change occurs.
On the tab entitled TM Dashboard, the Team Managers select their name from a dropdown box and all the Advisors reporting to that manager are then listed in a table which looks up the corresponding values for AHT, ACW etc. This data is then displayed on a graph for each metric.
The manager will see a few graphs populated for their team members, for example,the first graph will be for AHT and will show all Advisors with their call handling time so comparisons can be made when comparing like with like across Advisors.
The next graph down will show their ACW so it can be compared among the team.
The next graph will compare the Advisor NPS and the final graph will show the Brand NPS.
At this point the job is done for the Team Managers.
The Operations Manager will select their name from a drop down list and this will provide the names of the Team Managers reporting in to them. This way, comparisons of AHT, ACW etc can be made at a team level.
Having pondered this for a while I think that I may need a tab for each Team Manager so that when the data is pasted in, each will manager's tab will auto-populate having a lookup function to pull their own team's data for each of the metrics. When they use the drop down box it pulls the information forward to the dashboard tab and generates the graphs.
When the Operations Managers select their name from the drop down box, it would pull the totals for each Team Manager, again so comparisons can be made.
I suspect that index and match are the functions needed but I do not know enough about these at the present time to move this project forward.
I've attached the start of a spreadsheet of what I believe this may look like to see if this helps.
Many thanks of any thoughts and guidance.
Will