[SOLVED] Excel - Tracker Spreadsheet

[daddystabz]

I work in an office where myself and 2 others handle escalations. I created a spreadsheet a while back to track these cases for each individual agent. The spreadsheet gives each agent their own tab where they input all the case info for each case and then there is a cover tab that keeps track of how many open and closed cases there are for each individual, as well as for the department. I ran into issues with my cover sheet because we do everything at my job on an old version of Excel (2016) using the shared workbook function so we can all share the same workbook over a networked drive. However, I created the workbook in the newest version of Excel at my home before I realized Micorsoft had moved away from shared workbooks. Because my spreadsheet used the 'Form' button added to the Quick Access Toolbar to enter info for each case, used pivot tables, had tables I converted to ranges, and a macro to refresh the stats it caused issues and would not allow me to share the document on the shared network drive.

I ended up making a basic version for each agent - 3 individual spreadsheets that each person uses to track their own individual cases and then those cases are tabulated on a cover tab according to how many total cases there are, which ones are open, and which ones are closed.

BUT NOW: My office has asked me to make a new master stat tracker spreadsheet that will keep track of the cases we have according to type of case (Certificate issue, Promo issues, etc), date range (week of), total # of cases open, and how many were opened and closed within that date range. I am clueless as to where to even start with this.

An example of the spreadsheet I currently use for each of us is attached below.

If anyone would be willing to help me with this and/or chat with me over Skype to give me ideas on how to do this, I'd greatly appreciate it! I am even willing to pay a fee for your help!

 

[USAFRet]

This cries out for a database, not a spreadsheet.

I see this ALL the time. Starts in Excel, because the rows and columns look easy. Very quickly, it escalates beyond a spreadsheet or the users capabilities.

Excel is a great calculator
Word is a great typewriter
Access can be an OK database.

Easy shared access, relational tables, multiperson editing, etc, etc.
Not an Excel workbook.

Access 201XX has a built in template for exactly this

 

[Ralston18]

Excel is , at heart, a spreadsheet.

Yet, Excel has evolved to the point where it can also fulfil a variety of database functions,.

Excel is not a database.

My thought is to consider using Microsoft Access.

Use Access to link into the Excel tables (Important - use Read Only) to capture data and generate stats, etc..

How to start:

Create a new Access Data base that links (RO) to the Excel spreadsheets. Use the Microsoft Access Wizards to do so.

That establishes the basic tables.

Then, within Access, start creating customized queries and reports based on data drawn from the Excel spreadsheets.

Your queries and reports can be tailored as required for any individual Access User without impacting the source data.

I once did much the same for a group of my Program Managers who needed marketing data (maintained by the company's Marketing Departing in Excel) selected and sorted and reported with respect to what the Program Managers needed.

"Open Kimono".

 

[daddystabz]

Unfortunately, the company I work for is technologically behind the times and does not equip the company PCs with Access.

 

[Ralston18]

Will the company allow you to purchase Office 365 licensing?

Check into the options and costs with respect to your requirements and environment.

Three users: primarily to be using Excel with existing tables linked (and that can be RO) with Access.

Make a business case for the license purchase.

Very likely you will be able to easily demonstrate the time and savings gained by equipping the three of you with Office 365 (Access and Excel both in that) or just with Access as a fall back.

Versus continuing to work on, expand, and continually tweak/repair an overgrown Excel "database".

Too many times ( per @USAFRet) I also saw people start out with an Excel "database" that very quickly became a monster to work with. Especially if multiple people needed the same or similar data, selected, sorted, and presented in different personalized ways. And those monsters became a Gordian Knot that actually impeded people's ability to work and perform.

Yet mention using an actual database app and panic sets in. Too many folks remain with the mindset that a databases are complex, mysterious, difficult, etc.. Databases can be complex but a well designed database can actually be much easier to work with and maintain.

Overall Access is quite powerful right up front especially if you first let the wizards set things up and then tweak/customize for more specific requirements.

And with a built in Issue Tracking - wow. Good chance that you will be able to easily import the data from Excel. Then customize some thereafter.

Databases are much more friendly about doing such things.

Make the case.

 

[Corwin65]

If your office has that many employees, and cannot factor in the cost of one 365 license, into the cost of doing business then personally, I'd run as far and as fast as I could from them.

It's the same as buying office supplies or paying rent and utilities.

 

[USAFRet]

In Access, with just the absolute default "Issue Template" shown above:

Creating and managing an Issue

Report of all Open Issues

That is all built into that template.