Help creating an auto populating list from one worksheet to another based on criteria.

Hello Everyone,

I'd like to begin by saying I've been visiting this site over the last few weeks and I'm really impressed with the members and general communities way of helping each other, sometimes this help and advice isn't readily available. From the responses and questions I've seen it's appeared to be one of the friendlies communities I've seen in a long time, It's awesome. As an Excel Noob I've avoided coming here to ask questions without attempting the task on my own, but I feel I've come to a point where I might need some expert advice, so here's my situation...

I've made this sheet from a generic template I found online, and changed it up a little with a few odds and ends as I found ways to make the list quick, simple and to the point... yet still provide a way to see whats issues are important.

Excel Sheet Goal: To provide a main company task list that employees can reference to see what issues are due and what priorities are important.

Task 1.
- Create a separate worksheet for each employee that references the master list and displays an identical list with only their tasks (Based on the "Assigned To" column)...essentially similar to a filter I guess. The only reason I want separate sheets is for permissions. Currently the file resides in SharePoint and all users have read only permissions except the two delegates who assign and mark tasks as completed. If the users have separate sheets they can update their progress or completion status as they need to and the delegated individuals can mark them off the master list when they check on the employees progress. If there's a better way to do this please feel free to let me know.

Task 2.
- Autosort Due Date & Priority. I already have an auto filter in place for this(Due Date filtered first then priority), but I was hoping there was a way to automate this in realtime as data was entered instead of having to manually reapply the filter. The other problem I am being faced with is when I try to filter the priorities by cell color, as soon as one of the tasks are completed and closed the cell colors are removed and for some reason this messes up my Sort list, I have to then re setup the list up all over again. It's like it loses the setting as soon as one criteria is not there to reference.

C1 2012 Task List.xlsx

There are a few more goals I have for this project, but I feel these are my biggest setbacks at the moment and would love some advice or suggestions to overcoming these obstacles. Thanks again! If you have any questions regarding the sheet I pieced together just ask.

Hi

First thing, how about you correct the circular reference in column H. Won't get too far with that causing problems.

If you create a sheet for each of the "assigned to" employees, you could use a worksheet activate event that will bring in the data from the master sheet every time it is activated. Part of this process would be to then sort the items by priority. If you went down that track, then you would need some process that would return any user inut back to the master sheet.

Does this sound viable for your situation?

rylo

Originally Posted by rylo

Hi

First thing, how about you correct the circular reference in column H. Won't get too far with that causing problems.

If you create a sheet for each of the "assigned to" employees, you could use a worksheet activate event that will bring in the data from the master sheet every time it is activated. Part of this process would be to then sort the items by priority. If you went down that track, then you would need some process that would return any user inut back to the master sheet.

Does this sound viable for your situation?

rylo

Hello Rylo,

I did see the circular reference error the other day, but only from another computer. I solved it by enabling iterative calculation on that computer in excel, is this a bad way of doing it? [Excel>File>Options>Formulas>Enable iterative calculation].

As far as your second comment, that's exactly what I want to do! I want a way to delegate a task > then the employee marks their status for each task > this reflects on the master sheet where it can be marked as complete by a delegated individual. The only columns I wouldn't want employees to edit would be K "Due Date" and O "Closed By". Columns L & N should be their main way to update the status for each task and these columns would "hopefully" be able to update on the master list.

If you notice, when closing an issue in column O it will remove formatting in all the other cells for that row, if it's possible I'd like to prevent these from showing up in the users task list once they are marked closed...just so they're seeing what they need to see. This isn't that important but it would be nice if it were possible. Currently I have the filter set to move these closed tasks to the very bottom of the list when the auto filter is reapplied.

Hi

I'd change the formula as I think what you are trying to do is look at K5.

Please Login or Register  to view this content.

I made the change, then realised that you have a heap of them across the workbook. I can change the date ones, but where is there something like Due Time (to match the due date) that is hard coded.

rylo

The Due Date should not be automated like the Open Date, Task Started or Close Date because it's a date that's determined by whoever is opening the task. For example I might open a task and assign it to an employee but it might not be due until 1 or two weeks from now. However the Open/Close Date and Open/Close Time had to be in separate columns unlike the "Task Started" column because I have it being referenced in the Log Chart worksheet that graphs the open and closed issues. For some reason it won't work with the NOW() formula so I had to use TODAY() in a separate column.

The purpose of utilizing the Due Date with the priorities is to show what issues are important on the days they are due....so the really important issues are addressed first rather than just a list of stuff to do for the day. Think of the Priorities as a time during the day and the due date as the day itself....twice as productive.

I did just notice a goof I had in column (M) "Task Started" though, it should be this because it's supposed to timestamp the cell each time they update the status for the task....so you know the status and when they last worked on it.

Please Login or Register  to view this content.