Case Management in Excel

New Contributor

Hi all, I'm working on to improve our Excel tracker as a case management tool. Every month we have thousands of data to be added to manage. Each batch of data (at least 1000 data/entries) requires at least 10 different tasks/actions with target dates anytime within the span of 10 months which means we need to go back to this file every month so we can track whether a particular task or action has been completed. Currently, our actual file is using 60 columns. Our file is saved in a Sharepoint as we do not use local shared folder and the said file will be used and updated simultaneously by all of our 10 members. I can use Pivot in order to simplify the completion reporting but by any chance, do you have other suggestions we can explore on where management can be a little bit easier and efficient? See attached my sample data.

2 Replies

@tanberdul  First I want to thank you and commend you on creating a sample sheet, there is no way I would have any idea where to even start without it.  That said, your request is still quite vague but I'll make 2 suggestions:

a) format the data range as a table.  This will give you the ability to easily filter ranges to find what you need.

b) i think it may be useful to add a column at the end that counts how many of the completion dates are empty.  There are a lot of ways of adding this up and I created one that is sort of flexible in that you can expand it and it also counts the number of target dates, but there may be easier ways.  BUT the point is that having that column will let you easily filter out all of the 0's (i.e. all the rows that are done).  you could also sort based on rows that have the most left.

c) you could also add a column that has the oldest incomplete target date and again you can sort by this column to address those that are most overdue or soon to be.  And again I came up with a complicated formula but it seems to work

 

see attached

Hi mtarler, thank you so much for your suggestion. That's actually a great one having another column to track open tasks and which one is the oldest. I am conscious though if this will make our Excel sheet a little bit bigger or slower to load in adding these formulas since we use Sharepoint then all of my teammates are using and updating the file all at the same time. Nevertheless, I will give it a try since we have new batch of data to come in this December. Thanks so much again!