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
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
