• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Assistance with look up functions

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
 

Attachments

  • Manager Dashboard.xlsx
    22.9 KB · Views: 6
Hi William,

Please check this file your requiremnt.

Regards
Jawahar Prem
 

Attachments

  • Manager_Dashboard(1).xlsx
    25 KB · Views: 7
Hi Jawahar,

Thank you so much for taking the time to assist and reply.

The file you provided is part way there for sure but I'm looking a couple of slight differences.

The capture of the AHT, ACW etc is great and exactly what I need it to do.

The list of advisors I provided was just to put some data in to play around with. In reality, the department I'm hoping to put this in place for has around 300 advisors across approximately 20 teams.

Is it possible that when a manager is selected using the dropdown box, just the people in that team are shown and those not on the team are not shown? So, for example, using the data I have provided, when on the TM Dashboard tab David Gent is selected, you will see Andy Hulme and his figures at the top, David Crosby and his figures next and then that is it. )All other managers would have just one person because that's all I've knocked together). This way all the data is together on the screen and is easy for printing purposes.

On the OM Dashboard tab, when an Operations Manager is selected can the table behave in the same way as I've mentioned above? So just the data relating to the managers reporting to him or her will be shown.

Also for the same tab, I've amended David Crosby on the data tab to report to David Gent and then Steve Wilson. In doing so I can see that the AHT for Andy Hulme and David Crosby is added together (283+577 = 860). The team performance is the average handling time across the team so I would need to see this as being 430. I'm sorry, I wasn't very clear on this from the outset.

Are these amendments at all possible?

Kind regards

Will
 
Hi,
in your file u have OM="Steve Wilson" and "Craig Lynn" to "David Gent" that's why we didnt get the total count, Please check now.
 

Attachments

  • Manager_Dashboard(1).xlsx
    25 KB · Views: 5
Hi Jawaha,

You are absolutely correct, that is the way I put the original information with David Gent reporting to 2x different OM. That way part of his figures would show on Steve Wilson and part on Craig Lynn's data. The routing of the data from advisor to TM to OM worked perfectly as you had it.

What I was referring to in my last message was a minor change that I did to test the result and that was to place David Gent (and therefore his advisors Andy Hulme and David Crosby) fully under Steve Wilson. The result would need to be an average (283+577)/2 and therefore read 430 as opposed to just an adding of the total AHT.

I was apologising for not mentioning sooner that the Team Manager's AHT, ACW, NPS Advisor and NPS Brand would be an averages across the team and not a cumulative figure.

Sorry for being unclear.

Kind regards

Will
 
I'm 99% + sure that you can do all you want
without
a
single
formula​
(or code for that matter).

In the attached, goto the OM Dashboard sheet, cell H3, and select your OM.
If you choose Craig Lynn, you'll see David Gent's AHT is 577. Choose Steve Wilson as OM and David Gent's AHT becomes 283. Choose to see all OMs and David Gent's AHT becomes 430 (his average).

Now, alter your source data as before (putting David Gent's entries all under Steve Wilson), then go back to the OM Dashboard and right-click cell H3 and choose Refresh, then choose just Steve Wilson as OM then you'll see David Gent's AHT becomes 430.

If you go to the TM Dashboard sheet, and in cell R3 choose your TM (I've left it showing all of them in the attached), then you'll see the tables below update along with their charts.

Yes, it's all done with bog standard pivot tables.
 

Attachments

  • Chandoo34443Manager_Dashboard.xlsx
    47.5 KB · Views: 8
Another option,

See attached file.

Regards
Bosco

Hi Bosco,

Thank you so much for the reply.

Apologies for my late response but I've only just had a moment to log on.

On first glance this looks great... very interesting indeed. I'll get a proper chance to have a look and play tomorrow.

I just didn't want to leave a response / acknowledgement until then.

Kind regards

Will
 
I'm 99% + sure that you can do all you want
without
a
single
formula​
(or code for that matter).

In the attached, goto the OM Dashboard sheet, cell H3, and select your OM.
If you choose Craig Lynn, you'll see David Gent's AHT is 577. Choose Steve Wilson as OM and David Gent's AHT becomes 283. Choose to see all OMs and David Gent's AHT becomes 430 (his average).

Now, alter your source data as before (putting David Gent's entries all under Steve Wilson), then go back to the OM Dashboard and right-click cell H3 and choose Refresh, then choose just Steve Wilson as OM then you'll see David Gent's AHT becomes 430.

If you go to the TM Dashboard sheet, and in cell R3 choose your TM (I've left it showing all of them in the attached), then you'll see the tables below update along with their charts.

Yes, it's all done with bog standard pivot tables.


-----------------------------
Hi p45cal!

This is also very interesting and I thank you so much for your time and effort.

I'm not going to get a chance to have a proper play around with it until tomorrow.

As soon as I can do that I'll send another response.

Kind regards

Will
 
Hi Bosco and p45cal,

I've had a look at both of your options and they both look great!

I used to think I was ok at Excel until I saw the standard of work that is generated on this site... I have sooo much to learn. I am truly humbled.

p45cal, your option is probably the more simple of the 2. I say simple but don't mean that too literally as it has shown me that pivot tables are so much more powerful than the basic ones I use at the moment. I need to review yours to see how you got the outcome you did. In the workplace it would work fine but I suspect it would see me pulling the figures for each of the managers because even if I sent them the spreadsheet I can't see them pulling the data as they generally want to be spoon fed.

Bosco, your option is bang on the money here. When the names are changed via the drop box, the data changes and I can link this to the graphs so they change and format the page for printing by me of the respective managers. Your's is more complex because of the formulae used. I'm not at all familiar with the aggregate function.

Could I ask, the team sizes would generally be around 15 and would probably not exceed 20 plus, whilst the department has a population of advisors of around 300, on the approach to events such as mother's day, Easter, Christmas the department can swell significantly to around 900, so to cover any contingency, restructure or anything unforeseen, is it easy to do the following:-

- increase the potential team sizes to up to 30 advisors so their data can
be pulled through to the TM manager tab in the data table there?
- add / delete new managers as they are appointed or move / leave?
- make provision for the swell of the department up to say 900 advisors?

If you could give me some guidance on the above I would appreciate it as I do not want to start changing formulae that I do not understand as I'll just mess it all up.

Then, just to confirm my understanding of how this can best work for me. I would summarise this as follows:-

- This can be saved as a template.
- If I get daily data with the headings of Operations Manager, Team Manager,
Advisor, AHT, ACW, NPS Advisor and NPS Brand, I can paste it in to the data
tab and it will pull through from there
- The data will pull though to the TM Dashboard tab when the respective team
manager name is selected.
- The graphs will update when the data changes.
- The managers can print their own dashboards out ready for meetings etc.
- The Operations Managers can pull the data on their TM's and have a similar
output as with the TM's, just with TM summary data as opposed to
advisor data

Have I got that all correct?

I would like to thank you both and indeed Jawahar very much as will simplify my role in work.

Kind regards

Will
 
I'm 99% + sure that you can do all you want
without
a
single
formula​
(or code for that matter).

In the attached, goto the OM Dashboard sheet, cell H3, and select your OM.
If you choose Craig Lynn, you'll see David Gent's AHT is 577. Choose Steve Wilson as OM and David Gent's AHT becomes 283. Choose to see all OMs and David Gent's AHT becomes 430 (his average).

Now, alter your source data as before (putting David Gent's entries all under Steve Wilson), then go back to the OM Dashboard and right-click cell H3 and choose Refresh, then choose just Steve Wilson as OM then you'll see David Gent's AHT becomes 430.

If you go to the TM Dashboard sheet, and in cell R3 choose your TM (I've left it showing all of them in the attached), then you'll see the tables below update along with their charts.

Yes, it's all done with bog standard pivot tables.


Hi Bosco and p45cal,

I've had a look at both of your options and they both look great!

I used to think I was ok at Excel until I saw the standard of work that is generated on this site... I have sooo much to learn. I am truly humbled.

p45cal, your option is probably the more simple of the 2. I say simple but don't mean that too literally as it has shown me that pivot tables are so much more powerful than the basic ones I use at the moment. I need to review yours to see how you got the outcome you did. In the workplace it would work fine but I suspect it would see me pulling the figures for each of the managers because even if I sent them the spreadsheet I can't see them pulling the data as they generally want to be spoon fed.

Bosco, your option is bang on the money here. When the names are changed via the drop box, the data changes and I can link this to the graphs so they change and format the page for printing by me of the respective managers. Your's is more complex because of the formulae used. I'm not at all familiar with the aggregate function.

Could I ask, the team sizes would generally be around 15 and would probably not exceed 20 plus, whilst the department has a population of advisors of around 300, on the approach to events such as mother's day, Easter, Christmas the department can swell significantly to around 900, so to cover any contingency, restructure or anything unforeseen, is it easy to do the following:-

- increase the potential team sizes to up to 30 advisors so their data can
be pulled through to the TM manager tab in the data table there?
- add / delete new managers as they are appointed or move / leave?
- make provision for the swell of the department up to say 900 advisors?

If you could give me some guidance on the above I would appreciate it as I do not want to start changing formulae that I do not understand as I'll just mess it all up.

Then, just to confirm my understanding of how this can best work for me. I would summarise this as follows:-

- This can be saved as a template.
- If I get daily data with the headings of Operations Manager, Team Manager,
Advisor, AHT, ACW, NPS Advisor and NPS Brand, I can paste it in to the data
tab and it will pull through from there
- The data will pull though to the TM Dashboard tab when the respective team
manager name is selected.
- The graphs will update when the data changes.
- The managers can print their own dashboards out ready for meetings etc.
- The Operations Managers can pull the data on their TM's and have a similar
output as with the TM's, just with TM summary data as opposed to
advisor data

Have I got that all correct?

I would like to thank you both and indeed Jawahar very much as will simplify my role in work.

Kind regards

Will
 
Hello Mr.Basco.
i was searching this formula in Index, very thanks this will usfull for my next types of report.

Reagards
Jawahar Prem
 
Hello Mr.P54Cal,

Please let me know how to bring "Slicer though PVt" can u helo me on same.

Thanks.
Select any cell in a pivot table, in the ribbon, go to the Options tab of the Pivot Table Tools tab, in the Sort & Filter section, choose Insert Slicer, Insert Slicer…, tick the fields you want slicers for, click OK.
If you want the slicer to be connected to multiple pivot tables, you can right click on the slicer and choose Pivot table connections, and tick any pivot tables you want to be under control of that slicer.
 
Back
Top