Consolidate task status cells onto master rollup sheet?

[KPKilburn]

I've been searching for a solution on the board since yesterday, but can't seem to find an answer on how to do this... (please bear with me, it's lengthy)

First, here is what I have setup so far:

I have an employee task tracking matrix that is layed out like this (with explanations of what goes into them and how):

Columns:
A: Level

This is a drop-down list (pulled form another worksheet) that has 4 levels: Main Task, Section Responsible, Section Task, and Subtask. It is referenced by B for conditional formatting.

B: Tasks

This is where the staff inputs their respective tasks. The drop-down list in A causes a format to these blocks (just for asthetics -- a simple cell fill).

C: Suspense

This is what I put in. Eventually, I want to compare this with today's date (or perhaps a few days prior to turn it to "amber" status) so that I can have visibility of what's coming up.

D: Start

Manually entered.

E: Finish

Manually entered upon completion of the task or subtask.

F: Status

Red, Amber, Green selected from a list on another worksheet. Conditional formatting turns the cell the same color as the status (and leaves the words in for B&W printing).

G: Owner. Just for reference.

H: Priority.

Critical, High, Medium, and Low. Conditional formatting red, orange, amber (low is not formatted).

I: Remarks.

***

What I want to do is somehow "find" all of the major tasks (based on Level 1 of Column A) and compile them onto another worksheet in the same file. I want to list them along with their status "bubbles" (symbol or just cell fill) beside them.

I could do this easily if the task list were locked, but the staff members must be able to add rows without affecting anything else (it's a dynamic, semi-perpetual project, so new things come up daily).

The status should be pulled from Column F (conditional formatting that just carries over). I would like to have an automatic override for the status that turns it red if it's overdue (based on suspense) regardless of what status has been input.

I was thinking something along the lines of "COUNTIF" to find the tasks, but I'm just not adept enough to figure it out. I don't know VB, so if that's the only way to do it, could you please add enough comments so I can modify it if necessary (I know some programming -- BASIC, Inform -- but not to any great level of proficiency).

Another nice-to-have feature would be the ability to sort tasks by priority, but keep them "grouped" (since each section has tasks and subtasks, I don't know how to keep them together under the correct parent during a sort).

Thanks in advance for your help.

 

[KPKilburn]

Sorry, I forgot something...

I would also like to color code the status based also on the priority.

For example, an overdue status is red and shows up in the status cell (mentioned above).

An uncompleted task for a critical priority would turn yellow 2 weeks out (on the suspense cell itself, not the status cell). For a high priority, it wouldn't turn yellow until 1 week out. Medium priority would turn it yellow 3 days out. We don't have too many low priorities, so I'm not too concerned with those.

Thanks again.

 

[KPKilburn]

Before anyone answers the second part (coloring suspense cell as deadline approaches based on priority), I figured it out.

=AND(C4>TODAY(), E4=0, OR(AND(H4="Critical", C4-TODAY() <= 14), AND(H4="High", C4-TODAY()<=7), AND(H4="Medium", C4-TODAY()<= 3)))

Maybe not the most efficient way to do it, but it works.

 

[KPKilburn]

I figured all of it out except for the part where I can use a formula for conditional formatting from a different spreadsheet. Apparently, Excel doesn't allow you to reference a sheet other than the one you're in.

Is this possible another way? I want a rollup sheet that is triggered off of results in another sheet?