Dashboard best way to go?

**bgriffin4316** · 11-13-2018, 04:54 PM

Hey guys!!

I've got quick question for you and hopefully I'm placing this in the appropriate area. In short, I've been tasked with creating a quick, efficient way to view sales call data on a monthly, quarterly, and yearly basis. We currently have a running call log (that this forum helped me build - MANY THANKS) that gets updated daily with new information. This call log tracks name, date, and product called on.

We are trying to break down the data from this call log to be able to see how many calls/contacts we made in one month and for what line of business (we're in insurance) we were calling about.

In the research that I've been able to do thus far, it would seem that a Dashboard, or interactive Dashboard, might be the best way to go with this, but I'm an Excel newbie and know very little about what I'm doing. Given that the data in our call log is mostly all text-based (i.e. names, dates, and text for products/line-of-business, is it even possible to create a Dashboard? I've tried creating a couple of charts for this but keep showing up with nothing as the data I'm trying to track/show is in text form.

Is there a better way to achieve this goal? Or am I on the right track?

I would greatly appreciate any and all insight that might be able to be given towards this matter! Furthermore, IF this is the best way to go, and somebody has the know-how/reference material for me to be able to learn how to do this, please include that as well.

Thank you guys so much!!

Many thanks,

Ben

**bgriffin4316** · 11-15-2018, 10:44 AM

Thoughts anybody?

**Sam Capricci** · 11-15-2018, 11:06 AM

you might get more help if you post a sample of what you have now.

**bgriffin4316** · 11-15-2018, 12:45 PM

Great idea, Sambo kid.

I'm attaching a demo copy of my current call log. All names in the list are fictional characters for reference data. My actual call log has over 1,600 calls logged thus far and the year isn't over, so the actual data amount is much larger than what is in the demo.

Quick side note: The way the call log works is this: Click the Launch Call Log button, which brings up a userform into which you place a name, date, call type, LOB (line of business), and comments (if needed). Then, hit "Save" and the data inputted will be saved to both the Tracking Log page and the Month-specific page (based upon the date entered in the Date field).

Hope this helps some!

Ben

**JeteMc** · 11-21-2018, 12:40 AM

There are a lot of websites and/or videos that address the topic of Excel dashboards. One thing that I believe you will find they advise is that the old standby column chart is understandable and therefore a good choice.
On sheet2 there is a column chart which is tied to a drop down in cell B1 (populated from a list in column XFB) and a table in columns XFC:XFD.
In column XFC there is a unique list of LOB's.
Column XFD is populated using:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**bgriffin4316** · 12-06-2018, 06:41 PM

JeteMc, Thank you for that! That's headed in the right direction! I've attached to this a screenshot of what I've got so far. This is more just a visual representation, there's very little under the hood that's been done to make this a reality yet. The idea is that you select which month you would like to view and then each category gets filled in/calculated based upon the data in that month's worksheet/tab. This way you could quickly view where most of your time was spent. Hope this helps.

I stumbled my way through getting the center chart set up based upon data from the November tab in my example project (which has more data input than the one I uploaded here). Also, the Line of Business Totals are calculated based upon information in the November tab.

Thank you all in advance for your help with this!!

***PS - Since this is a running, constantly being updated list, I need to figure out to get it to where the data in updated based upon an ever-growing list of data instead of a set range (hopefully that makes sense).

Many thanks,

Ben

**JeteMc** · 12-06-2018, 11:38 PM

I recommend that the chart and totals be be linked to the 'tracking log' sheet instead of each individual month's tab.
The formulas that count LOB and call type use whole column references so that you'll be able to track 1.04 million calls.
The formula for LOB is: =COUNTIFS('Tracking Log'!B:B,">="&C$4,'Tracking Log'!B:B,"<="&EOMONTH(C$4,0),'Tracking Log'!D:D,A9)
The formula for contact type is: =COUNTIFS('Tracking Log'!B:B,">="&C$4,'Tracking Log'!B:B,"<="&EOMONTH(C$4,0),'Tracking Log'!C:C,A16)
You can also automatically fill the month tab information using the following formula (modeled only on the November tab):

Formula:

Please Login or Register  to view this content.

**bgriffin4316** · 12-07-2018, 02:28 PM

JeteMc,

I REALLY appreciate your help with this! I've been trying this morning to implement this and I think I'm getting somewhere slowly. Will continue with it and let you know how it goes!

Ben

**bgriffin4316** · 12-07-2018, 06:05 PM

For some reason, when I plug in the formulas you've provided, it keeps returning a value of 0. To my knowledge, I've done it all as you have in the example sheet. What do think I could be missing that would cause this?

**JeteMc** · 12-07-2018, 07:54 PM

The formula looks correct, however it is hard to trouble shoot a screen shot.
If it is possible please upload the workbook so that we may attempt to resolve the issue.
Let us know if you have any questions.

Dashboard best way to go?

LinkBack

Thread Tools

Rate This Thread

Display

Dashboard best way to go?

Re: Dashboard best way to go?

Re: Dashboard best way to go?

Re: Dashboard best way to go?

Re: Dashboard best way to go?

Re: Dashboard best way to go?

Re: Dashboard best way to go?

Re: Dashboard best way to go?

Re: Dashboard best way to go?

Re: Dashboard best way to go?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Dashboard

Dashboard

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions