Using Excel as Work-History / Tracking tool

[jh0]

Good afternoon,

I'm working on a project now that involves setting up a service desk that will be responsible for tracking and guiding individuals through a certain process. It is going to be a specialized desk that only deals with a few dozen items a year, so it doesn't make sense to use a commercial workflow / history tracking application. I am assuming that Excel can be used to substitute such a program. I was wondering if anyone has done this before and if there are any tips or good sources to get started with?

I think the most important things that I can think of would be generic comment history with time-stamping and even user names engrained on the comments, but I'm not sure how much work I'm getting myself into here, or how feasible something like this is.

Any help is appreciated,
Thank you,
~Joe

 

[pplstuff]

Yes, excel can do everything you have mentioned. How much work will be involved depends on your needs. I have no links to provide, but I'm guessing you'll need to figure out what you want the end product to look like and what degree of functionality is required FIRST. When building a tool like this, it's best to begin with the end in mind. Once you know where it's headed, you can begin to ask specific questions on certain functions you're having trouble with.

What do you want it to do? Will you be storing data in a database? Will the data be held in Excel or Access (I'm guessing the same excel workbook, since you mention you'll only use this tool a few times per annum)?

Good luck!

 

[jh0]

Thanks pplstuff,

Still getting clarity on the long term project at hand--but I believe that the use of this tool / tracking system will remain the same. On the surface, I am thinking about having everything built onto and housed on templates that will be opened and used to track every new client. Within each template there is going to be a decision tool built into the book, but that should be its own beast and not affect this portion. There will be a master page that summarizes history and information about a client, and links to other more specific individual cases related to that client and their entire workbook (These "cases" will be reside on other tabs in the workbook). I think that there will need to be some data linking to get the book to work together smoothly, but that is down the road (I.E. Selecting a product from a dropdown menu, generating a random and unique number, say, 1 cell to the right and then adding a new worksheet on the book with the same number with a preset template style.)

On a higher, reporting oriented level, I've been thinking about having a way to grab any "Important" data as needed. I figure that this could be done simply enough by just writing specific macros to search from every workbook in the folder in which these will be stored and write the cell contents as an array to a new workbook.

As far as the history goes, I'm pretty sure that all I am going to be looking to do in this case is have a field on each sheet (Both the master sheets and the more granular case by case sheets) that will accept input by a user, track the time and login name of the author of the comment, and generate a log (protected and uneditable) on the excel worksheet.

I am relatively new to VBA, and I think that this is the only way to do most of what I have mentioned, unless there are some great excel features that I have overlooked. I also have no Idea how long something like this might take. It seems like it only required a 1 time build and can then be pumped out mass production style, but who knows.

If anyone has tackled something like this and has any words of wisdom, they would be greatly appreciated.

Thanks in advance,
~Joe