Workflow spreadsheet development
Budget: $30 – $250 AUD
Hi there! I work in government, with a team of officers responsible for a list of portfolio areas and I'm trying to find a way to keep track of tasks and projects, and a way to keep my managers in the loop. The spreadsheet should track projects in one table, and tasks for each project in another. It should know which officer owns which portfolio area in dependant data validation lists and scan the project table for all active projects in a drop down list when new tasks are entered, and who the responsible manager is for each portfolio area using lookup formulas. Portfolio allocations for managers and officers should be located on their own spreadsheet and should be easily customisable. When a new task or project is entered into a data entry row, it should automatically copy that data into a new row generated at the bottom of the table and clear the data entry row for re-use. When data is entered or changed in both tables, data should automatically sort by project or task status, and then by deadline (ascending). Projects and tasks in both tables that are marked "complete" or "done" should be automatically copied and cleared into a separate archive table (preferably somewhere on the same sheet out of sight but still easily accessible possibly accessed using a button. If this is not possible, it can be located on another worksheet). The final desired outcome is for each officer to have their own worksheet tracking projects and tasks, with a manager dashboard worksheet (or multiple) that tracks which portfolio areas have the most active tasks/projects and tracks the volume/complexity/deadlines of projects and tasks by officer.
The attached spreadsheet only has some of the functionalities described, and what functions are included only operate on the project table, not the task table. Currently it can generate a new row into the project table and sort the data in the desired order, but nothing else. There are also errors I cant correct, including that data is copied and cleared from the data entry range when only one cell is filled, and row sizes in the task table change each time a new row is added. Its not ideal, but hopefully it provides some basis for what I'm after. Happy to provide further information as required.
The attached spreadsheet only has some of the functionalities described, and what functions are included only operate on the project table, not the task table. Currently it can generate a new row into the project table and sort the data in the desired order, but nothing else. There are also errors I cant correct, including that data is copied and cleared from the data entry range when only one cell is filled, and row sizes in the task table change each time a new row is added. Its not ideal, but hopefully it provides some basis for what I'm after. Happy to provide further information as required.