Automate Excel Tracking Form

This form is used to track the yearly workload of staff. Staff can have up to 100 cases and they need to be aware of when things are due or coming up. Currently, the form is broken down into 3 main parts: Current Cases, Monthly overview and To Do List.

Current Cases

Shows the current caseload for a specific staff. This doesn't need much altering. The other 2 sections are based on the information provided in this part. In this section, the critical items are 65th birthday, 6798E, Semi and Annual. All the work will be based on the impact of the next 2 sections when I add a new case onto this section. The Goal of this project is once I add a new case to this list, the other 2 sections, Monthly Overview and To Do List should automatically add the client information to the list.

Monthly Overview

This simply shows what months things are due. It gives a basic overview. The current problem is that sometimes adding new cases causes it to break and by not showing the proper colors. Proper colors are based on the column the data is pulled from.

It is based on the data from Current Cases: Columns E, K, L and M. Right now the attached spreadsheet only shows the name, but not the color. I need it to not break and show whatever color I want (though not a priority).

To Do List:

This is the most important part. Currently, whenever I add a new case, I have to manually add the information by hand which is time-consuming and frustrating.

The To-Do List, similar to the Monthly Overview is broken down by Month.

It is based on the information on Columns A, E, K, L, and M. For example: Kent, Clark has things due on April (6798E), February (Semi), & July (Annual). If you look at the To-Do List, there are X on matching what is due. Annuals are colored Yellow, so if you look at Kent, Clark in July all the yellow stuff will have an X. The staff's job is to change it to a date once they have completed the task. The goal and purpose of this section are, instead of having to manually enter the X, the spreadsheet should automatically fill in this information. If you look at Kent, Clark for April there is only an X for the 6798E. If you look at February the 2 red columns have an X for Kent, Clark. So the columns in the To-Do List is color coded to match the colors from the Current Cases.

The goal of this project is once I add a new case to the Current Cases, the other 2 sections, Monthly Overview, and To-Do List should automatically add the client information whenever I add a new case in Current Cases. The manager will need to be able to add more columns in the To-Do List section and still have the spreadsheet not fail. How you could about it doesn’t matter, but it can only remain in Excel it needs to add stuff. The project should assume that day-to-day users are idiots. So it can be a push of a button to confirm the changes. If a person is remove then they get removed from the other 2 sections.

Excel VBA Excel Macros C++ programiranje

ID projekta: #32029713

Več o projektu