Hi All
I'm looking for some advice or guidance on the best method of breaking down the creation of a task manager in Excel. I work as the medicolegal officer in a hospital and I deal with several different types of requests (subpoena's, medical report, ROI's etc). For each of these requests I record the surname, medical record number, date received, date due, and other general comments (which includes files requested, date requested etc).
Currently I have the worksheet sorted vertically by each type of request with a line in between like this
Name MRN Date Rec Date Due Comments
Medical Report
Subpoena
ROI
The problem is that the information that I am collecting in the comments section is getting larger as I try to add more information to aid in completing my tasks more efficiently. In my preliminary thinking about this problem I have realised that each type of request (subpoena, ROI etc) has very different information that I record and help to make decisions about what I need to do. For example, Subpoena's have a very distinct timeframe and so it's important to collate the files in time to have them sent and at the court before their due date. For ROI's processing does not begin until payment is received so sending out invoices and sending reminders is more important.
I began to think that the best way to organise it was to create a worksheet for each specific type of request as that way I could record the specific information that I would require. But this means that I now need somehow create a summary page in which I can see all the tasks that need my immediate attention and I have no idea how to do this in Excel. Each specific type of request will have a different subset of criteria that determines whether they need attention.
For instance for subpoena's I need to know all the ones that have to be sent in the next week so that I can chase any notes that are missing. Or for ROI's I need to know which ones have been paid and copied and are ready to be read. Also for ROI's I need to know any for which I've invoiced but haven't received money so that I can send a reminder invoice after a set period of time.
I really have no idea where to start with this and would appreciate some pointers on what I should consider. I have looked around and read some stuff but the problem I have is that most of what I'm trying to do is date based and I can't get my head around how to make Excel do stuff with dates.
I'm looking for some advice or guidance on the best method of breaking down the creation of a task manager in Excel. I work as the medicolegal officer in a hospital and I deal with several different types of requests (subpoena's, medical report, ROI's etc). For each of these requests I record the surname, medical record number, date received, date due, and other general comments (which includes files requested, date requested etc).
Currently I have the worksheet sorted vertically by each type of request with a line in between like this
Name MRN Date Rec Date Due Comments
Medical Report
Subpoena
ROI
The problem is that the information that I am collecting in the comments section is getting larger as I try to add more information to aid in completing my tasks more efficiently. In my preliminary thinking about this problem I have realised that each type of request (subpoena, ROI etc) has very different information that I record and help to make decisions about what I need to do. For example, Subpoena's have a very distinct timeframe and so it's important to collate the files in time to have them sent and at the court before their due date. For ROI's processing does not begin until payment is received so sending out invoices and sending reminders is more important.
I began to think that the best way to organise it was to create a worksheet for each specific type of request as that way I could record the specific information that I would require. But this means that I now need somehow create a summary page in which I can see all the tasks that need my immediate attention and I have no idea how to do this in Excel. Each specific type of request will have a different subset of criteria that determines whether they need attention.
For instance for subpoena's I need to know all the ones that have to be sent in the next week so that I can chase any notes that are missing. Or for ROI's I need to know which ones have been paid and copied and are ready to be read. Also for ROI's I need to know any for which I've invoiced but haven't received money so that I can send a reminder invoice after a set period of time.
I really have no idea where to start with this and would appreciate some pointers on what I should consider. I have looked around and read some stuff but the problem I have is that most of what I'm trying to do is date based and I can't get my head around how to make Excel do stuff with dates.
