I have been dwelling on this for some time to no avail; any suggestions would
be gratefully received. Also, please forgive the sparse use of €˜technical
terms€¦

I have a large spreadsheet that, when finished, will be used as the main
data dump of training status for employees. Along the top (F:EO) is the list
of employee names, and down the side (4:67) is a list of all procedures
needed. The middle section is the corresponding date that a procedure was
trained to an employee along with the appropriate colour: Green +Date =
training valid; Yellow + Date = Training Incomplete/Superseded; or Red +
Blank [no date] = Training Required. All other non corresponding cells are
Grey.

Each Procedure has a €˜Revision Number, and when the number is updated (only
ever upwards, i.e. 1 then 2 then 3) all Training Date cells [non-blank??] on
that row should be changed to yellow.

What is the best way of automating this? If buttons, would you suggest a
command button for each procedure or a drop- down list??


Thanks in advance for any help,

Phin

Apologies if I am being unclear,

I suppose what Im asking is this: Is there a way for a macro to colour fill
selected ranges, and if so how. And could this macro be activated by change
in a cell (e.g. €œ6€� being changed to €œ7€�).

I think I can figure out how to set the cells, using conditional formatting,
so that €œIF cell A1 6 change cell B1 to yellow€�. But how can I then reset
this to the newly entered number? So that the formula is then IF cell A1 7
change cell B1 to yellow€�.
Also, using this, can the colour formatting be temporarily overridden, so
that the user can change B1 to green manually?


If this is still as murky as a peat bog, please ask any questions so that it
is easier to hone in on the specifics...

Thanks in advance for your help


"Phin Doyle" wrote:

I have been dwelling on this for some time to no avail; any suggestions would
be gratefully received. Also, please forgive the sparse use of €˜technical
terms€¦

I have a large spreadsheet that, when finished, will be used as the main
data dump of training status for employees. Along the top (F:EO) is the list
of employee names, and down the side (4:67) is a list of all procedures
needed. The middle section is the corresponding date that a procedure was
trained to an employee along with the appropriate colour: Green +Date =
training valid; Yellow + Date = Training Incomplete/Superseded; or Red +
Blank [no date] = Training Required. All other non corresponding cells are
Grey.

Each Procedure has a €˜Revision Number, and when the number is updated (only
ever upwards, i.e. 1 then 2 then 3) all Training Date cells [non-blank??] on
that row should be changed to yellow.

What is the best way of automating this? If buttons, would you suggest a
command button for each procedure or a drop- down list??


Thanks in advance for any help,

Phin

You don't need a macro, conditional formatting will do it. select C4 to the
last cell and use a formula of

=AND(C4<"",$B4=MAX(IF($A$4:$A$20=$A4,$B$4:$B$20)) )

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Phin Doyle" wrote in message
...

Apologies if I am being unclear,

I suppose what I'm asking is this: Is there a way for a macro to colour
fill
selected ranges, and if so how. And could this macro be activated by
change
in a cell (e.g. "6" being changed to "7").

I think I can figure out how to set the cells, using conditional
formatting,
so that "IF cell A1 6 change cell B1 to yellow". But how can I then reset
this to the newly entered number? So that the formula is then IF cell A1
7
change cell B1 to yellow".
Also, using this, can the colour formatting be temporarily overridden, so
that the user can change B1 to green manually?


If this is still as murky as a peat bog, please ask any questions so that
it
is easier to hone in on the specifics...

Thanks in advance for your help


"Phin Doyle" wrote:

I have been dwelling on this for some time to no avail; any suggestions
would
be gratefully received. Also, please forgive the sparse use of
'technical
terms'.

I have a large spreadsheet that, when finished, will be used as the main
data dump of training status for employees. Along the top (F:EO) is the
list
of employee names, and down the side (4:67) is a list of all procedures
needed. The middle section is the corresponding date that a procedure
was
trained to an employee along with the appropriate colour: Green +Date =
training valid; Yellow + Date = Training Incomplete/Superseded; or Red +
Blank [no date] = Training Required. All other non corresponding cells
are
Grey.

Each Procedure has a 'Revision Number', and when the number is updated
(only
ever upwards, i.e. 1 then 2 then 3) all Training Date cells
[non-blank??] on
that row should be changed to yellow.

What is the best way of automating this? If buttons, would you suggest a
command button for each procedure or a drop- down list??


Thanks in advance for any help,

Phin

Hi Bob,

Thanks for your help, but I think ive made an error somewhere and my utter
lack knowledge with formulas doesnt help matters..

What is that this formula does? I see it it has something to do with the
correlation of Columns A and B. But when i mess about with the values of
cells the highlighting becomes irratic.

What I need is a single cell/column referance to the revision Number:

NAME Revision EmployeeA EmployeeB EmployeeC
Procedure A 5 12/8/05 5/2/03 7/4/06
Procedure B 4 20/11/99 9/1/06
Procedure C 22 12/4/06 15/7/02

So when Procedure A is updated to Revision 6 the dates become Yellow.
(Blanks are already filled red )

Then, as training is completed new dates are entered and Manually (if need
be) changed to green; untill the procedure updates again.



"Bob Phillips" wrote:

You don't need a macro, conditional formatting will do it. select C4 to the
last cell and use a formula of

=AND(C4<"",$B4=MAX(IF($A$4:$A$20=$A4,$B$4:$B$20)) )

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Phin Doyle" wrote in message
...

Apologies if I am being unclear,

I suppose what I'm asking is this: Is there a way for a macro to colour
fill
selected ranges, and if so how. And could this macro be activated by
change
in a cell (e.g. "6" being changed to "7").

I think I can figure out how to set the cells, using conditional
formatting,
so that "IF cell A1 6 change cell B1 to yellow". But how can I then reset
this to the newly entered number? So that the formula is then IF cell A1
7
change cell B1 to yellow".
Also, using this, can the colour formatting be temporarily overridden, so
that the user can change B1 to green manually?


If this is still as murky as a peat bog, please ask any questions so that
it
is easier to hone in on the specifics...

Thanks in advance for your help


"Phin Doyle" wrote:

I have been dwelling on this for some time to no avail; any suggestions
would
be gratefully received. Also, please forgive the sparse use of
'technical
terms'.

I have a large spreadsheet that, when finished, will be used as the main
data dump of training status for employees. Along the top (F:EO) is the
list
of employee names, and down the side (4:67) is a list of all procedures
needed. The middle section is the corresponding date that a procedure
was
trained to an employee along with the appropriate colour: Green +Date =
training valid; Yellow + Date = Training Incomplete/Superseded; or Red +
Blank [no date] = Training Required. All other non corresponding cells
are
Grey.

Each Procedure has a 'Revision Number', and when the number is updated
(only
ever upwards, i.e. 1 then 2 then 3) all Training Date cells
[non-blank??] on
that row should be changed to yellow.

What is the best way of automating this? If buttons, would you suggest a
command button for each procedure or a drop- down list??


Thanks in advance for any help,

Phin

My formula should work as given with your data. If checks that the date cell
is not blank, and also that the revision number is the max for that
procedure. If you added some indicator to the dates, you could conditionally
format the completed tasks.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Phin Doyle" wrote in message
...
Hi Bob,

Thanks for your help, but I think ive made an error somewhere and my utter
lack knowledge with formulas doesnt help matters..

What is that this formula does? I see it it has something to do with the
correlation of Columns A and B. But when i mess about with the values of
cells the highlighting becomes irratic.

What I need is a single cell/column referance to the revision Number:

NAME Revision EmployeeA EmployeeB EmployeeC
Procedure A 5 12/8/05 5/2/03 7/4/06
Procedure B 4 20/11/99 9/1/06
Procedure C 22 12/4/06 15/7/02

So when Procedure A is updated to Revision 6 the dates become Yellow.
(Blanks are already filled red )

Then, as training is completed new dates are entered and Manually (if need
be) changed to green; untill the procedure updates again.



"Bob Phillips" wrote:

You don't need a macro, conditional formatting will do it. select C4 to
the
last cell and use a formula of

=AND(C4<"",$B4=MAX(IF($A$4:$A$20=$A4,$B$4:$B$20)) )

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Phin Doyle" wrote in message
...

Apologies if I am being unclear,

I suppose what I'm asking is this: Is there a way for a macro to
colour
fill
selected ranges, and if so how. And could this macro be activated by
change
in a cell (e.g. "6" being changed to "7").

I think I can figure out how to set the cells, using conditional
formatting,
so that "IF cell A1 6 change cell B1 to yellow". But how can I then
reset
this to the newly entered number? So that the formula is then IF cell
A1
7
change cell B1 to yellow".
Also, using this, can the colour formatting be temporarily overridden,
so
that the user can change B1 to green manually?


If this is still as murky as a peat bog, please ask any questions so
that
it
is easier to hone in on the specifics...

Thanks in advance for your help


"Phin Doyle" wrote:

I have been dwelling on this for some time to no avail; any
suggestions
would
be gratefully received. Also, please forgive the sparse use of
'technical
terms'.

I have a large spreadsheet that, when finished, will be used as the
main
data dump of training status for employees. Along the top (F:EO) is
the
list
of employee names, and down the side (4:67) is a list of all
procedures
needed. The middle section is the corresponding date that a
procedure
was
trained to an employee along with the appropriate colour: Green
+Date =
training valid; Yellow + Date = Training Incomplete/Superseded; or
Red +
Blank [no date] = Training Required. All other non corresponding
cells
are
Grey.

Each Procedure has a 'Revision Number', and when the number is
updated
(only
ever upwards, i.e. 1 then 2 then 3) all Training Date cells
[non-blank??] on
that row should be changed to yellow.

What is the best way of automating this? If buttons, would you
suggest a
command button for each procedure or a drop- down list??


Thanks in advance for any help,

Phin

Right then,

The Formuals working now,

what sort of indictators would you suggest? Is there a way it could be
removed automatically once a revision is updated?

Do you think it would make life simpler if I add a referance column that
states the date of the revision? Then I could use this as the higher lower
figure. What are your thoughts??



"Bob Phillips" wrote:

My formula should work as given with your data. If checks that the date cell
is not blank, and also that the revision number is the max for that
procedure. If you added some indicator to the dates, you could conditionally
format the completed tasks.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Phin Doyle" wrote in message
...
Hi Bob,

Thanks for your help, but I think ive made an error somewhere and my utter
lack knowledge with formulas doesnt help matters..

What is that this formula does? I see it it has something to do with the
correlation of Columns A and B. But when i mess about with the values of
cells the highlighting becomes irratic.

What I need is a single cell/column referance to the revision Number:

NAME Revision EmployeeA EmployeeB EmployeeC
Procedure A 5 12/8/05 5/2/03 7/4/06
Procedure B 4 20/11/99 9/1/06
Procedure C 22 12/4/06 15/7/02

So when Procedure A is updated to Revision 6 the dates become Yellow.
(Blanks are already filled red )

Then, as training is completed new dates are entered and Manually (if need
be) changed to green; untill the procedure updates again.



"Bob Phillips" wrote:

You don't need a macro, conditional formatting will do it. select C4 to
the
last cell and use a formula of

=AND(C4<"",$B4=MAX(IF($A$4:$A$20=$A4,$B$4:$B$20)) )

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Phin Doyle" wrote in message
...

Apologies if I am being unclear,

I suppose what I'm asking is this: Is there a way for a macro to
colour
fill
selected ranges, and if so how. And could this macro be activated by
change
in a cell (e.g. "6" being changed to "7").

I think I can figure out how to set the cells, using conditional
formatting,
so that "IF cell A1 6 change cell B1 to yellow". But how can I then
reset
this to the newly entered number? So that the formula is then IF cell
A1
7
change cell B1 to yellow".
Also, using this, can the colour formatting be temporarily overridden,
so
that the user can change B1 to green manually?


If this is still as murky as a peat bog, please ask any questions so
that
it
is easier to hone in on the specifics...

Thanks in advance for your help


"Phin Doyle" wrote:

I have been dwelling on this for some time to no avail; any
suggestions
would
be gratefully received. Also, please forgive the sparse use of
'technical
terms'.

I have a large spreadsheet that, when finished, will be used as the
main
data dump of training status for employees. Along the top (F:EO) is
the
list
of employee names, and down the side (4:67) is a list of all
procedures
needed. The middle section is the corresponding date that a
procedure
was
trained to an employee along with the appropriate colour: Green
+Date =
training valid; Yellow + Date = Training Incomplete/Superseded; or
Red +
Blank [no date] = Training Required. All other non corresponding
cells
are
Grey.

Each Procedure has a 'Revision Number', and when the number is
updated
(only
ever upwards, i.e. 1 then 2 then 3) all Training Date cells
[non-blank??] on
that row should be changed to yellow.

What is the best way of automating this? If buttons, would you
suggest a
command button for each procedure or a drop- down list??


Thanks in advance for any help,

Phin

I see Bob is pretty well taking care of your questions, but I thought I would
give you another perspective. I made a similar "Training Matrix" thing for a
client, but didn't use color because they wanted to make copies and not have
to use a color copier. Their Matrix differs in that the Tasks are across the
top and the Employee Names down the side. INstead of dates on the matrix, we
use blank for "no training", O for "In Training", 1 for "Trained", and 2 for
"Qualified to Train others in this task".....each could have an "x" following
it which indicates that the training received has passed it's "expiration
date". There are 42 of these sheets, (all in the same workbook), one for
each Department/Shift. All the data is kept in a master database on one
sheet and transferred to the Sheet of interest at the time of interrogation,
otherwise, all sheets are kept hidden. This is all controlled off a master
Menu sheet with buttons. The working field of each sheet is automatically
cleared upon Sheet De-activation, while the names and tasks are
retained......so each time each sheet is brought up, it contains the most
recent data on the database, along with a DateStamp. Each sheet also
contains information to show if each task has at least 3 people trained in
that area. The file is about 9meg in size and contains approx 15,000 rows in
the master database.

This program has been working for about 4 years, through several upgrades.
It is maintained by an employee with normal Excel skills and no VBA required
on her part.. I thought you might be interested to see how someone else did
it.

Vaya con Dios,
Chuck, CABGx3




"Phin Doyle" wrote:

I have been dwelling on this for some time to no avail; any suggestions would
be gratefully received. Also, please forgive the sparse use of €˜technical
terms€¦

I have a large spreadsheet that, when finished, will be used as the main
data dump of training status for employees. Along the top (F:EO) is the list
of employee names, and down the side (4:67) is a list of all procedures
needed. The middle section is the corresponding date that a procedure was
trained to an employee along with the appropriate colour: Green +Date =
training valid; Yellow + Date = Training Incomplete/Superseded; or Red +
Blank [no date] = Training Required. All other non corresponding cells are
Grey.

Each Procedure has a €˜Revision Number, and when the number is updated (only
ever upwards, i.e. 1 then 2 then 3) all Training Date cells [non-blank??] on
that row should be changed to yellow.

What is the best way of automating this? If buttons, would you suggest a
command button for each procedure or a drop- down list??


Thanks in advance for any help,

Phin

Mucho Gusto Chuck,

Thanks for taking an interest, as you might of gussed ive been learning this
stuff as I go, and although Ive been doing Ok, some guidance would be welcome
recieved.

I have been thinking of transposing my Employee/ Tasks to permit longevity,
as employees well outnumber the tasks. Although I will probably stick with
color coding, I am intrigued by the navigation and operation of you database
- and the use of a "Date Stamp".


Would there be any way I could have a look at an example of this?

Gracias por su socorro!





"CLR" wrote:

I see Bob is pretty well taking care of your questions, but I thought I would
give you another perspective. I made a similar "Training Matrix" thing for a
client, but didn't use color because they wanted to make copies and not have
to use a color copier. Their Matrix differs in that the Tasks are across the
top and the Employee Names down the side. INstead of dates on the matrix, we
use blank for "no training", O for "In Training", 1 for "Trained", and 2 for
"Qualified to Train others in this task".....each could have an "x" following
it which indicates that the training received has passed it's "expiration
date". There are 42 of these sheets, (all in the same workbook), one for
each Department/Shift. All the data is kept in a master database on one
sheet and transferred to the Sheet of interest at the time of interrogation,
otherwise, all sheets are kept hidden. This is all controlled off a master
Menu sheet with buttons. The working field of each sheet is automatically
cleared upon Sheet De-activation, while the names and tasks are
retained......so each time each sheet is brought up, it contains the most
recent data on the database, along with a DateStamp. Each sheet also
contains information to show if each task has at least 3 people trained in
that area. The file is about 9meg in size and contains approx 15,000 rows in
the master database.

This program has been working for about 4 years, through several upgrades.
It is maintained by an employee with normal Excel skills and no VBA required
on her part.. I thought you might be interested to see how someone else did
it.

Vaya con Dios,
Chuck, CABGx3




"Phin Doyle" wrote:

I have been dwelling on this for some time to no avail; any suggestions would
be gratefully received. Also, please forgive the sparse use of €˜technical
terms€¦

I have a large spreadsheet that, when finished, will be used as the main
data dump of training status for employees. Along the top (F:EO) is the list
of employee names, and down the side (4:67) is a list of all procedures
needed. The middle section is the corresponding date that a procedure was
trained to an employee along with the appropriate colour: Green +Date =
training valid; Yellow + Date = Training Incomplete/Superseded; or Red +
Blank [no date] = Training Required. All other non corresponding cells are
Grey.

Each Procedure has a €˜Revision Number, and when the number is updated (only
ever upwards, i.e. 1 then 2 then 3) all Training Date cells [non-blank??] on
that row should be changed to yellow.

What is the best way of automating this? If buttons, would you suggest a
command button for each procedure or a drop- down list??


Thanks in advance for any help,

Phin

Hi Phin.........
Sorry, I really can't send the file, as it contains sensitive information to
the Client, and stripping the sensitive material out would render the program
just a useless shell. But, I can discuss any aspect of the programming, as
it's all conventional stuff. The program opens to a sheet called the
MainMenu. It has a few "housekeeping" buttons to Save, SaveAndExit, Archive,
Help, etc etc. Then it has an array of 42 buttons arranged alphabetically.
Each of these buttons activates a small macro to unhide and "goto" the
respective sheet.

Once on a selected matrix sheet, the user may edit the tasks and/or employee
names. Then the user presses an "Update" button which runs a macro to go to
the database and retireve all relative info and fill in the matrix. The same
macro also puts the current date permanently in a "Last Revised" cell, so the
information on the matrix is married to a specific point in time. Normal,
fixed formulas count the number of qualified entries for each task and enters
this in a header row, which is then summarized in a "Percent 3-deep" cell for
the entire sheet. This capability can also be turned off and on for each
task by the user so certain tasks are not considered in this tally. There is
also a "ReturnToMainMenu" button there which will clear the matrix field,
hide the sheet, and return the user to the MainMenu.

Also included in the workbook is the MasterDatabase which lists each
employee, and all relevant information about their training for an individual
task. Each employee is listed on a separate entry for each task they might
be trained for. Employees sometimes qualify for tasks under different
departments....no matter to the database. Employees sometimes leave the
company and return later, no matter to the database. There name is removed
from a matrix when they leave and the name and data stays in the database.
When they return and their name is added to a matrix, their information is
automatically called up.

There is also a custom "Help" sheet which defines the overall basic
operation of the file, and explains in detail how each button works. And, a
push-button feature that allows Archiving of the entire file to a separate
directory and appends the filename with a date/time. There is a "Sandbox"
button on the MainMenu that will create a separate file, exactly like the
real one, except that it is named differently, does not have Save or Archive
buttons, and is totally divorced from the "real" file. The user is cancelled
out of the real file and put in this one at the push of the button....no
returning allowed. This Sandbox allows new users to become familiar with the
operation of the file without danger of hurting anything. A new "Sandbox" is
created with all current information, each time the button is pressed.

That's it in a nutshell. If you are interested in any particular feature,
just ask back.

Vaya con Dios,
Chuck, CABGx3



"Phin Doyle" wrote:

Mucho Gusto Chuck,

Thanks for taking an interest, as you might of gussed ive been learning this
stuff as I go, and although Ive been doing Ok, some guidance would be welcome
recieved.

I have been thinking of transposing my Employee/ Tasks to permit longevity,
as employees well outnumber the tasks. Although I will probably stick with
color coding, I am intrigued by the navigation and operation of you database
- and the use of a "Date Stamp".


Would there be any way I could have a look at an example of this?

Gracias por su socorro!





"CLR" wrote:

I see Bob is pretty well taking care of your questions, but I thought I would
give you another perspective. I made a similar "Training Matrix" thing for a
client, but didn't use color because they wanted to make copies and not have
to use a color copier. Their Matrix differs in that the Tasks are across the
top and the Employee Names down the side. INstead of dates on the matrix, we
use blank for "no training", O for "In Training", 1 for "Trained", and 2 for
"Qualified to Train others in this task".....each could have an "x" following
it which indicates that the training received has passed it's "expiration
date". There are 42 of these sheets, (all in the same workbook), one for
each Department/Shift. All the data is kept in a master database on one
sheet and transferred to the Sheet of interest at the time of interrogation,
otherwise, all sheets are kept hidden. This is all controlled off a master
Menu sheet with buttons. The working field of each sheet is automatically
cleared upon Sheet De-activation, while the names and tasks are
retained......so each time each sheet is brought up, it contains the most
recent data on the database, along with a DateStamp. Each sheet also
contains information to show if each task has at least 3 people trained in
that area. The file is about 9meg in size and contains approx 15,000 rows in
the master database.

This program has been working for about 4 years, through several upgrades.
It is maintained by an employee with normal Excel skills and no VBA required
on her part.. I thought you might be interested to see how someone else did
it.

Vaya con Dios,
Chuck, CABGx3




"Phin Doyle" wrote:

I have been dwelling on this for some time to no avail; any suggestions would
be gratefully received. Also, please forgive the sparse use of €˜technical
terms€¦

I have a large spreadsheet that, when finished, will be used as the main
data dump of training status for employees. Along the top (F:EO) is the list
of employee names, and down the side (4:67) is a list of all procedures
needed. The middle section is the corresponding date that a procedure was
trained to an employee along with the appropriate colour: Green +Date =
training valid; Yellow + Date = Training Incomplete/Superseded; or Red +
Blank [no date] = Training Required. All other non corresponding cells are
Grey.

Each Procedure has a €˜Revision Number, and when the number is updated (only
ever upwards, i.e. 1 then 2 then 3) all Training Date cells [non-blank??] on
that row should be changed to yellow.

What is the best way of automating this? If buttons, would you suggest a
command button for each procedure or a drop- down list??


Thanks in advance for any help,

Phin

Buenos Dias,

Muy Impresionante! I have been planning something along the same lines but
nowhere near as sophisticated. I would really appreciate it if you wouldnt
mind going over some of these terms and features, and perhaps point me in the
right direction so I might build a more exciting and, more importantly,
permanent matrix.

Are these €œhousekeeping€� buttons instead of normal toolbar options?
How does the €œUpdate€� button retrieve the relevant data? €“ Is it something
like the €˜find tool?
What is the €œPercent 3-deep€� cell?
€œName is removed from the matrix when leave but stays on database€�. How are
you differentiating between the €˜matrix and €˜the master database?

I love this idea of a help menu. How should I go about finding out how to
make them? Are they €˜custom userform dialog boxes?

Would you recommend the use of a €œSandbox€�?

One thing that I would like to do is have a €œNew Staff Member€� attribute,
which would insert the member within the ordered system of Department/ Shift/
Name. How should I start developing this?

If you only answer half of these you will help no end.

Muchas Recuerdos,

Phin


"CLR" wrote:

Hi Phin.........
Sorry, I really can't send the file, as it contains sensitive information to
the Client, and stripping the sensitive material out would render the program
just a useless shell. But, I can discuss any aspect of the programming, as
it's all conventional stuff. The program opens to a sheet called the
MainMenu. It has a few "housekeeping" buttons to Save, SaveAndExit, Archive,
Help, etc etc. Then it has an array of 42 buttons arranged alphabetically.
Each of these buttons activates a small macro to unhide and "goto" the
respective sheet.

Once on a selected matrix sheet, the user may edit the tasks and/or employee
names. Then the user presses an "Update" button which runs a macro to go to
the database and retireve all relative info and fill in the matrix. The same
macro also puts the current date permanently in a "Last Revised" cell, so the
information on the matrix is married to a specific point in time. Normal,
fixed formulas count the number of qualified entries for each task and enters
this in a header row, which is then summarized in a "Percent 3-deep" cell for
the entire sheet. This capability can also be turned off and on for each
task by the user so certain tasks are not considered in this tally. There is
also a "ReturnToMainMenu" button there which will clear the matrix field,
hide the sheet, and return the user to the MainMenu.

Also included in the workbook is the MasterDatabase which lists each
employee, and all relevant information about their training for an individual
task. Each employee is listed on a separate entry for each task they might
be trained for. Employees sometimes qualify for tasks under different
departments....no matter to the database. Employees sometimes leave the
company and return later, no matter to the database. There name is removed
from a matrix when they leave and the name and data stays in the database.
When they return and their name is added to a matrix, their information is
automatically called up.

There is also a custom "Help" sheet which defines the overall basic
operation of the file, and explains in detail how each button works. And, a
push-button feature that allows Archiving of the entire file to a separate
directory and appends the filename with a date/time. There is a "Sandbox"
button on the MainMenu that will create a separate file, exactly like the
real one, except that it is named differently, does not have Save or Archive
buttons, and is totally divorced from the "real" file. The user is cancelled
out of the real file and put in this one at the push of the button....no
returning allowed. This Sandbox allows new users to become familiar with the
operation of the file without danger of hurting anything. A new "Sandbox" is
created with all current information, each time the button is pressed.

That's it in a nutshell. If you are interested in any particular feature,
just ask back.

Vaya con Dios,
Chuck, CABGx3



"Phin Doyle" wrote:

Mucho Gusto Chuck,

Thanks for taking an interest, as you might of gussed ive been learning this
stuff as I go, and although Ive been doing Ok, some guidance would be welcome
recieved.

I have been thinking of transposing my Employee/ Tasks to permit longevity,
as employees well outnumber the tasks. Although I will probably stick with
color coding, I am intrigued by the navigation and operation of you database
- and the use of a "Date Stamp".


Would there be any way I could have a look at an example of this?

Gracias por su socorro!





"CLR" wrote:

I see Bob is pretty well taking care of your questions, but I thought I would
give you another perspective. I made a similar "Training Matrix" thing for a
client, but didn't use color because they wanted to make copies and not have
to use a color copier. Their Matrix differs in that the Tasks are across the
top and the Employee Names down the side. INstead of dates on the matrix, we
use blank for "no training", O for "In Training", 1 for "Trained", and 2 for
"Qualified to Train others in this task".....each could have an "x" following
it which indicates that the training received has passed it's "expiration
date". There are 42 of these sheets, (all in the same workbook), one for
each Department/Shift. All the data is kept in a master database on one
sheet and transferred to the Sheet of interest at the time of interrogation,
otherwise, all sheets are kept hidden. This is all controlled off a master
Menu sheet with buttons. The working field of each sheet is automatically
cleared upon Sheet De-activation, while the names and tasks are
retained......so each time each sheet is brought up, it contains the most
recent data on the database, along with a DateStamp. Each sheet also
contains information to show if each task has at least 3 people trained in
that area. The file is about 9meg in size and contains approx 15,000 rows in
the master database.

This program has been working for about 4 years, through several upgrades.
It is maintained by an employee with normal Excel skills and no VBA required
on her part.. I thought you might be interested to see how someone else did
it.

Vaya con Dios,
Chuck, CABGx3




"Phin Doyle" wrote:

I have been dwelling on this for some time to no avail; any suggestions would
be gratefully received. Also, please forgive the sparse use of €˜technical
terms€¦

I have a large spreadsheet that, when finished, will be used as the main
data dump of training status for employees. Along the top (F:EO) is the list
of employee names, and down the side (4:67) is a list of all procedures
needed. The middle section is the corresponding date that a procedure was
trained to an employee along with the appropriate colour: Green +Date =
training valid; Yellow + Date = Training Incomplete/Superseded; or Red +
Blank [no date] = Training Required. All other non corresponding cells are
Grey.

Each Procedure has a €˜Revision Number, and when the number is updated (only
ever upwards, i.e. 1 then 2 then 3) all Training Date cells [non-blank??] on
that row should be changed to yellow.

What is the best way of automating this? If buttons, would you suggest a
command button for each procedure or a drop- down list??


Thanks in advance for any help,

Phin

Hi Phin..........

I have prepared the answers to most of your questions, but it's a little
lengthly to post here....so, if you want to send me your email addy, I will
send it to you.........

ATS

(put the CATS out)

Vaya con Dios,
Chuck, CABGx3



"Phin Doyle" wrote in message
...
Buenos Dias,

Muy Impresionante! I have been planning something along the same lines but
nowhere near as sophisticated. I would really appreciate it if you wouldn'
t
mind going over some of these terms and features, and perhaps point me in
the
right direction so I might build a more exciting and, more importantly,
permanent matrix.

Are these "housekeeping" buttons instead of normal toolbar options?
How does the "Update" button retrieve the relevant data? - Is it something
like the 'find' tool?
What is the "Percent 3-deep" cell?
"Name is removed from the matrix when leave but stays on database". How
are
you differentiating between the 'matrix' and 'the master database'?

I love this idea of a help menu. How should I go about finding out how to
make them? Are they 'custom userform dialog boxes'?

Would you recommend the use of a "Sandbox"?

One thing that I would like to do is have a "New Staff Member" attribute,
which would insert the member within the ordered system of Department/
Shift/
Name. How should I start developing this?

If you only answer half of these you will help no end.

Muchas Recuerdos,

Phin


"CLR" wrote:

Hi Phin.........
Sorry, I really can't send the file, as it contains sensitive
information to
the Client, and stripping the sensitive material out would render the
program
just a useless shell. But, I can discuss any aspect of the programming,
as
it's all conventional stuff. The program opens to a sheet called the
MainMenu. It has a few "housekeeping" buttons to Save, SaveAndExit,
Archive,
Help, etc etc. Then it has an array of 42 buttons arranged
alphabetically.
Each of these buttons activates a small macro to unhide and "goto" the
respective sheet.

Once on a selected matrix sheet, the user may edit the tasks and/or
employee
names. Then the user presses an "Update" button which runs a macro to
go to
the database and retireve all relative info and fill in the matrix. The
same
macro also puts the current date permanently in a "Last Revised" cell,
so the
information on the matrix is married to a specific point in time.
Normal,
fixed formulas count the number of qualified entries for each task and
enters
this in a header row, which is then summarized in a "Percent 3-deep"
cell for
the entire sheet. This capability can also be turned off and on for
each
task by the user so certain tasks are not considered in this tally.
There is
also a "ReturnToMainMenu" button there which will clear the matrix
field,
hide the sheet, and return the user to the MainMenu.

Also included in the workbook is the MasterDatabase which lists each
employee, and all relevant information about their training for an
individual
task. Each employee is listed on a separate entry for each task they
might
be trained for. Employees sometimes qualify for tasks under different
departments....no matter to the database. Employees sometimes leave the
company and return later, no matter to the database. There name is
removed
from a matrix when they leave and the name and data stays in the
database.
When they return and their name is added to a matrix, their information
is
automatically called up.

There is also a custom "Help" sheet which defines the overall basic
operation of the file, and explains in detail how each button works.
And, a
push-button feature that allows Archiving of the entire file to a
separate
directory and appends the filename with a date/time. There is a
"Sandbox"
button on the MainMenu that will create a separate file, exactly like
the
real one, except that it is named differently, does not have Save or
Archive
buttons, and is totally divorced from the "real" file. The user is
cancelled
out of the real file and put in this one at the push of the button....no
returning allowed. This Sandbox allows new users to become familiar
with the
operation of the file without danger of hurting anything. A new
"Sandbox" is
created with all current information, each time the button is pressed.

That's it in a nutshell. If you are interested in any particular
feature,
just ask back.

Vaya con Dios,
Chuck, CABGx3



"Phin Doyle" wrote:

Mucho Gusto Chuck,

Thanks for taking an interest, as you might of gussed ive been
learning this
stuff as I go, and although Ive been doing Ok, some guidance would be
welcome
recieved.

I have been thinking of transposing my Employee/ Tasks to permit
longevity,
as employees well outnumber the tasks. Although I will probably stick
with
color coding, I am intrigued by the navigation and operation of you
database
- and the use of a "Date Stamp".


Would there be any way I could have a look at an example of this?

Gracias por su socorro!





"CLR" wrote:

I see Bob is pretty well taking care of your questions, but I
thought I would
give you another perspective. I made a similar "Training Matrix"
thing for a
client, but didn't use color because they wanted to make copies and
not have
to use a color copier. Their Matrix differs in that the Tasks are
across the
top and the Employee Names down the side. INstead of dates on the
matrix, we
use blank for "no training", O for "In Training", 1 for "Trained",
and 2 for
"Qualified to Train others in this task".....each could have an "x"
following
it which indicates that the training received has passed it's
"expiration
date". There are 42 of these sheets, (all in the same workbook),
one for
each Department/Shift. All the data is kept in a master database on
one
sheet and transferred to the Sheet of interest at the time of
interrogation,
otherwise, all sheets are kept hidden. This is all controlled off a
master
Menu sheet with buttons. The working field of each sheet is
automatically
cleared upon Sheet De-activation, while the names and tasks are
retained......so each time each sheet is brought up, it contains the
most
recent data on the database, along with a DateStamp. Each sheet
also
contains information to show if each task has at least 3 people
trained in
that area. The file is about 9meg in size and contains approx
15,000 rows in
the master database.

This program has been working for about 4 years, through several
upgrades.
It is maintained by an employee with normal Excel skills and no VBA
required
on her part.. I thought you might be interested to see how someone
else did
it.

Vaya con Dios,
Chuck, CABGx3




"Phin Doyle" wrote:

I have been dwelling on this for some time to no avail; any
suggestions would
be gratefully received. Also, please forgive the sparse use of
'technical
terms'.

I have a large spreadsheet that, when finished, will be used as
the main
data dump of training status for employees. Along the top (F:EO)
is the list
of employee names, and down the side (4:67) is a list of all
procedures
needed. The middle section is the corresponding date that a
procedure was
trained to an employee along with the appropriate colour: Green
+Date =
training valid; Yellow + Date = Training Incomplete/Superseded; or
Red +
Blank [no date] = Training Required. All other non corresponding
cells are
Grey.

Each Procedure has a 'Revision Number', and when the number is
updated (only
ever upwards, i.e. 1 then 2 then 3) all Training Date cells
[non-blank??] on
that row should be changed to yellow.

What is the best way of automating this? If buttons, would you
suggest a
command button for each procedure or a drop- down list??


Thanks in advance for any help,

Phin