E
Excel-SNAFU
I'm rather new here and somewhat knowledgeable in Excel. This kind
explains how I was thrown under the bus at my job. I will go ahead an
say THANK YOU!!!! now to everyone who is willing to assist me in m
endeavor. My cry for help!!! is lengthy so I will break it up int
various stages and upload a "Draft Spreadsheet" to assist those who wis
to help me with this crazy assignment that was dumped upon me. And a
necessary I will keep updating my posts as we go along with th
development of this spreadsheet.
And 'here is a Draft file
(http://wikisend.com/download/258626/Dev-Draft.xlsx)
"Groups Tasked" and the association to the "Status"
1) "Groups Tasked" are in Columns H through M; "Status" is i
Column E.
2) If a group is not tasked they can either be designated with
"N" or a "blank field", both are acceptable. If no group is tasked the
"Not Assigned" is automatically inserted into the "Status" Column.
3) In the following order of precedence from the top being th
least priority and the last being the highest priority....
4) If any group is designated with an "A" then the "Status" Cel
will automatically be populated with "Assigned".
5) If any group is designated with an "T" then the "Status" Cel
will automatically be populated with "Tracking".
6) If any group is designated with an "I" then the "Status" Cel
will automatically be populated with "Incomplete".
7) If any group is designated with an "X" then the "Status" Cel
will automatically be populated with "Past Due".
8) If all designated cells, minus those that are populated wit
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 “Suspens
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 th
"Status" field is the following then the "Suspense Date" fields fil
color will adjust as follows:
a) Status is "Not Assigned" or "Assigned"....Date Field will b
"Red"
3) If the date is 18 days out from the suspense date and th
"Status" field is the following then the "Suspense Date" fields fil
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 th
“Status” field is the following then the “Suspense Date” fields fil
color will adjust as follows:
a) Status is “Not Assigned” or “Assigned”….Date Field will b
“Yellow”
POC Dropdown and population of the POC Fields
On the “Macros” Sheet is a “POC Information” Table. Column A i
utilized to generate the drop down list in Column F on the “Template
and “Sheet1-Example” tabs. The desired effect is once th
“Title/Position” has been selected from the drop down on th
aforementioned tabs then the following will automatically take place i
the various cells in Column F:
The top cell (OFFICE POC), instead of being populated by th
“Title/Position” will instead be populated with the actual “Name” tha
corresponds with the selected “Title/Position”.
Also the “Name” will be hyperlink to the “Email Address” tha
corresponds respectfully.
The bottom cell (CONTACT INFO) will automatically be populated with th
“Phone Number” that corresponds with the selected “Title/Position
explains how I was thrown under the bus at my job. I will go ahead an
say THANK YOU!!!! now to everyone who is willing to assist me in m
endeavor. My cry for help!!! is lengthy so I will break it up int
various stages and upload a "Draft Spreadsheet" to assist those who wis
to help me with this crazy assignment that was dumped upon me. And a
necessary I will keep updating my posts as we go along with th
development of this spreadsheet.
And 'here is a Draft file
(http://wikisend.com/download/258626/Dev-Draft.xlsx)
"Groups Tasked" and the association to the "Status"
1) "Groups Tasked" are in Columns H through M; "Status" is i
Column E.
2) If a group is not tasked they can either be designated with
"N" or a "blank field", both are acceptable. If no group is tasked the
"Not Assigned" is automatically inserted into the "Status" Column.
3) In the following order of precedence from the top being th
least priority and the last being the highest priority....
4) If any group is designated with an "A" then the "Status" Cel
will automatically be populated with "Assigned".
5) If any group is designated with an "T" then the "Status" Cel
will automatically be populated with "Tracking".
6) If any group is designated with an "I" then the "Status" Cel
will automatically be populated with "Incomplete".
7) If any group is designated with an "X" then the "Status" Cel
will automatically be populated with "Past Due".
8) If all designated cells, minus those that are populated wit
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 “Suspens
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 th
"Status" field is the following then the "Suspense Date" fields fil
color will adjust as follows:
a) Status is "Not Assigned" or "Assigned"....Date Field will b
"Red"
3) If the date is 18 days out from the suspense date and th
"Status" field is the following then the "Suspense Date" fields fil
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 th
“Status” field is the following then the “Suspense Date” fields fil
color will adjust as follows:
a) Status is “Not Assigned” or “Assigned”….Date Field will b
“Yellow”
POC Dropdown and population of the POC Fields
On the “Macros” Sheet is a “POC Information” Table. Column A i
utilized to generate the drop down list in Column F on the “Template
and “Sheet1-Example” tabs. The desired effect is once th
“Title/Position” has been selected from the drop down on th
aforementioned tabs then the following will automatically take place i
the various cells in Column F:
The top cell (OFFICE POC), instead of being populated by th
“Title/Position” will instead be populated with the actual “Name” tha
corresponds with the selected “Title/Position”.
Also the “Name” will be hyperlink to the “Email Address” tha
corresponds respectfully.
The bottom cell (CONTACT INFO) will automatically be populated with th
“Phone Number” that corresponds with the selected “Title/Position