T
TACP_DADDY
I have a spreadsheed created by my predasesors at work. It has two tabs,
several rows (each the name of an individual), and several columbs (each
lists a type of training).
Tab 1 simply has the date the individual completed the training (27-NOV-08).
For example John Doe on row 20 completed "xxx training" (columb A).
Tab 2 has a formula which references the same cell on "Tab 1" (row 20,
columb A). It also colors each cell (12 month currency for columb A. The
cell will color GREEN when it is within +12 months of the date completed,
YELLOW 3 months out, and turn RED once 12 months has passed).
This works very nicely so everyone can fin the row with thier name and see
what they are green on, coming due on, and expired on. However we now need
it to also report % complete on each item. I am not an Excel NINJA, and the
closest we have to NINJA status are also baffled on how to do this. It seems
like it would be extremely simple, but I have had no luck making it work in a
whole week.
Q: How would I make Columb A show a number by percentage (%) of who is
current and has not expired on the training?
-Somehow I need it to take the date they completed the training, have it add
12 months, and see if that date has passed or not. If not, then count them
as good. Repeat for every person. So if 80 out of 100 people have completed
the training within the last 12 months, it will display 80%. I can then
repeat the process for each columb (some are not 12 month cycles for
expiration).
PLEASE HELP!!!
-thanks
several rows (each the name of an individual), and several columbs (each
lists a type of training).
Tab 1 simply has the date the individual completed the training (27-NOV-08).
For example John Doe on row 20 completed "xxx training" (columb A).
Tab 2 has a formula which references the same cell on "Tab 1" (row 20,
columb A). It also colors each cell (12 month currency for columb A. The
cell will color GREEN when it is within +12 months of the date completed,
YELLOW 3 months out, and turn RED once 12 months has passed).
This works very nicely so everyone can fin the row with thier name and see
what they are green on, coming due on, and expired on. However we now need
it to also report % complete on each item. I am not an Excel NINJA, and the
closest we have to NINJA status are also baffled on how to do this. It seems
like it would be extremely simple, but I have had no luck making it work in a
whole week.
Q: How would I make Columb A show a number by percentage (%) of who is
current and has not expired on the training?
-Somehow I need it to take the date they completed the training, have it add
12 months, and see if that date has passed or not. If not, then count them
as good. Repeat for every person. So if 80 out of 100 people have completed
the training within the last 12 months, it will display 80%. I can then
repeat the process for each columb (some are not 12 month cycles for
expiration).
PLEASE HELP!!!
-thanks