I'm rather new here and somewhat knowledgeable in Excel. This kinda explains how I was thrown under the bus at my job. I will go ahead and say THANK YOU!!!! now to everyone who is willing to assist me in my endeavor. My cry for help!!! is lengthy so I will break it up into various stages and upload a "Draft Spreadsheet" to assist those who wish to help me with this crazy assignment that was dumped upon me. And as necessary I will keep updating my posts as we go along with the development of this spreadsheet.

And here is a Draft file


"Groups Tasked" and the association to the "Status"
1) "Groups Tasked" are in Columns H through M; "Status" is in Column E.
2) If a group is not tasked they can either be designated with a "N" or a "blank field", both are acceptable. If no group is tasked then "Not Assigned" is automatically inserted into the "Status" Column.
3) In the following order of precedence from the top being the least priority and the last being the highest priority....
4) If any group is designated with an "A" then the "Status" Cell will automatically be populated with "Assigned".
5) If any group is designated with an "T" then the "Status" Cell will automatically be populated with "Tracking".
6) If any group is designated with an "I" then the "Status" Cell will automatically be populated with "Incomplete".
7) If any group is designated with an "X" then the "Status" Cell will automatically be populated with "Past Due".
8) If all designated cells, minus those that are populated with either “N” or “blank field” have “C” in their field then the “Status” Cell will automatically be populated with “Complete”

"Status" Cell and the association to the "Suspense Date"
1) If the “Status” field is one of the following then the “Suspense Date” fields fill color will be as follows:
a) Status is “Tracking”….Date Field will be “Light Green”
b) Status is “Complete”….Date Field will be “Green”
c) Status is “Incomplete”….Date Field will be “Orange”
d) Status is “Past Due”….Date Field will be “Red”

2) If the date is 9 days out from the suspense date and the "Status" field is the following then the "Suspense Date" fields fill color will adjust as follows:
a) Status is "Not Assigned" or "Assigned"....Date Field will be "Red"

3) If the date is 18 days out from the suspense date and the "Status" field is the following then the "Suspense Date" fields fill color will adjust as follows:
a) Status is "Not Assigned"....Date Field will be "Red"
b) Status is "Assigned"....Date Field will be "Yellow"

4) If the date is 24 days out from the suspense date and the “Status” field is the following then the “Suspense Date” fields fill color will adjust as follows:
a) Status is “Not Assigned” or “Assigned”….Date Field will be “Yellow”

POC Dropdown and population of the POC Fields
On the “Macros” Sheet is a “POC Information” Table. Column A is utilized to generate the drop down list in Column F on the “Template” and “Sheet1-Example” tabs. The desired effect is once the “Title/Position” has been selected from the drop down on the aforementioned tabs then the following will automatically take place in the various cells in Column F:

The top cell (OFFICE POC), instead of being populated by the “Title/Position” will instead be populated with the actual “Name” that corresponds with the selected “Title/Position”.

Also the “Name” will be hyperlink to the “Email Address” that corresponds respectfully.

The bottom cell (CONTACT INFO) will automatically be populated with the “Phone Number” that corresponds with the selected “Title/Position”