Reminder/Follow-up system for Case Management using VBA

REUBEN

Board Regular
Joined
Mar 7, 2014
Messages
113
Hi All,

I've been developing a worksheet for case management at work and it has really taken good shape. Courtesy a lot of technical help from this forum! :)

I now want to automate reminders/follow-ups for different instances. I've tried the IF function for basic reminders in and added conditional formatting to make it look better. But the formula is not really a success.
For your reference the formula I use:
=IF(ISBLANK(O1);" ";(IF(TODAY()>O1+13;"Biometrics Date Due";(IF(Q1="Assignee Completed Biometrics";"Check Case Progress";" ")))))

As you see I'm giving it too different cell references to check and provide me alerts.

Anyways I believe the solution to this is a VBA code to automate such reminders/follow-ups.

To give you a brief idea about the worksheet, I have information about client's name in column D, Status updates in column Q, submission dates in O and the reminders/followups in column S.

I want to use the column to throw up all the alerts pertaining to each case row wise( each row is dedicated to a single client). It would be great to have this via a message box too but I'm guessing that would need more coding.

The 1st follow up is when date in cell O is 13 days old and the reminder "Biometrics Date Due". This must be over ruled if I change the status to "Assignee Completed Biometrics" in the status column Q or if I select "Case number received" in the corresponding S cell (where I was to actually receive the reminder). If these conditions are not met then the S cell must display "Check Case Progress". I have condtionally formatted these phrases in the column S this to look eye catchy. Need help with the checking though.

The next is a little complex. This is where I need help for case management. We are required to follow up with clients on cases that are in progress or open. And our follow ups must be every 3rd day if we do not hear back from the client. I'm not sure how to go about this. The work sheet is already very wide and adding more columns for follow up dates and calculating is just messy. Is there another work around? Subsequently I would want to set these reminders for each step of the case's progress by setting different reminder schedules, right till the end of each case.

Thanks for your patience for reading this detailed thread and your help! :)

Reuben
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

REUBEN

Board Regular
Joined
Mar 7, 2014
Messages
113
Hi All,

I worked on the above with a fried who's a programmer and he was able to help me with a code to make the above work so the first one is not an issue now.

I am though looking at help with the follow-up issue. How to I create a mechanism to create a follow-up system?

Thank you.
 
Upvote 0

Forum statistics

Threads
1,190,895
Messages
5,983,433
Members
439,843
Latest member
PlanetFitness

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top