I'm putting together a spreadsheet showing personnel recurring training dates
and whether they are overdue or current in that training. I have about 100
people with about 50 different training requirements that are recurring on
different timelines (ex: Mark Smith has to take CPR class every 20 months and
a driving course every 12 months). I'm trying to develop a function/format
combo that lets me type in the date they last had that training, paired with
the required frequency of the training, giving me a result of "CURRENT" or
"OVERDUE" and turning the cell green or red. I've played around with the
TODAY() function a little bit, but I can't seem to figure anything out.

On Sat, 23 Jan 2010 17:21:01 -0800, goskerz
wrote:

I'm putting together a spreadsheet showing personnel recurring training dates
and whether they are overdue or current in that training. I have about 100
people with about 50 different training requirements that are recurring on
different timelines (ex: Mark Smith has to take CPR class every 20 months and
a driving course every 12 months). I'm trying to develop a function/format
combo that lets me type in the date they last had that training, paired with
the required frequency of the training, giving me a result of "CURRENT" or
"OVERDUE" and turning the cell green or red. I've played around with the
TODAY() function a little bit, but I can't seem to figure anything out.

How best to do this may depend, in part, on your version of Excel.

Setup a table as follows:

A2:An Names
B1:xx1 Classes

Set up a lookup table someplace named Training Requirements.

Assuming your requirements can all be expressed in months (or years), list the
Class name in one column, with the number of months in the second column. These
names should match exactly the names in B1:xx1 (row 1)

e.g.

CPR 20
Driving 12

A2: Mark Smith
B1: CPR
C1: Driving

Then in B2:xx2 you will enter the date last taken

For a conditional formatting formula

Select B2
Use for Format 1 (green):
=AND(LEN(B2)0,EDATE(B2,VLOOKUP(B$1,TrainingRequir ements,2,FALSE))=TODAY())

Use for Format 2 (red):
=AND(LEN(B2)0,EDATE(B2,VLOOKUP(B$1,TrainingRequir ements,2,FALSE))<TODAY())

Then copy the formats to the applicable cells.

If you are using a version of Excel prior to 2007, you may get an error message
if you cannot use Analysis ToolPak functions. (I can't recall if they are
allowable in the older versions of Excel.

If that is the case, you will need to set up a matrix of cells someplace, using
the above formula, to generate the actual due dates. In that case, assume you
set this up in AB2:xxnn, then your conditional formatting formula would be, for
B2, something like: =and(len(b2)0,ab2=today()) for the green and
=and(len(b2)0,ab2<today()) for the red

--ron

Sir, I have a very good feeling about this. I think it's going to work, just
having a little bit of trouble... For the conditional formatting formulas,
where do I type those in? I went to manage rules, new rule then "use a
formula to determine which cells to format" I copied your first formula in
there but recieved an error message. I am running office 2007. I wish I was a
little smarter on Excel, but I'm most likely doing something wrong. Thank you
for your help!
"Ron Rosenfeld" wrote:

On Sat, 23 Jan 2010 17:21:01 -0800, goskerz
wrote:

I'm putting together a spreadsheet showing personnel recurring training dates
and whether they are overdue or current in that training. I have about 100
people with about 50 different training requirements that are recurring on
different timelines (ex: Mark Smith has to take CPR class every 20 months and
a driving course every 12 months). I'm trying to develop a function/format
combo that lets me type in the date they last had that training, paired with
the required frequency of the training, giving me a result of "CURRENT" or
"OVERDUE" and turning the cell green or red. I've played around with the
TODAY() function a little bit, but I can't seem to figure anything out.

How best to do this may depend, in part, on your version of Excel.

Setup a table as follows:

A2:An Names
B1:xx1 Classes

Set up a lookup table someplace named Training Requirements.

Assuming your requirements can all be expressed in months (or years), list the
Class name in one column, with the number of months in the second column. These
names should match exactly the names in B1:xx1 (row 1)

e.g.

CPR 20
Driving 12

A2: Mark Smith
B1: CPR
C1: Driving

Then in B2:xx2 you will enter the date last taken

For a conditional formatting formula

Select B2
Use for Format 1 (green):
=AND(LEN(B2)0,EDATE(B2,VLOOKUP(B$1,TrainingRequir ements,2,FALSE))=TODAY())

Use for Format 2 (red):
=AND(LEN(B2)0,EDATE(B2,VLOOKUP(B$1,TrainingRequir ements,2,FALSE))<TODAY())

Then copy the formats to the applicable cells.

If you are using a version of Excel prior to 2007, you may get an error message
if you cannot use Analysis ToolPak functions. (I can't recall if they are
allowable in the older versions of Excel.

If that is the case, you will need to set up a matrix of cells someplace, using
the above formula, to generate the actual due dates. In that case, assume you
set this up in AB2:xxnn, then your conditional formatting formula would be, for
B2, something like: =and(len(b2)0,ab2=today()) for the green and
=and(len(b2)0,ab2<today()) for the red

--ron
.

On Sun, 24 Jan 2010 10:14:01 -0800, goskerz
wrote:

Sir, I have a very good feeling about this. I think it's going to work, just
having a little bit of trouble... For the conditional formatting formulas,
where do I type those in? I went to manage rules, new rule then "use a
formula to determine which cells to format" I copied your first formula in
there but recieved an error message. I am running office 2007. I wish I was a
little smarter on Excel, but I'm most likely doing something wrong. Thank you
for your help!

Having 2007 makes things a bit easier.

What was the error message?

Did you properly NAME the lookup table?

What, exactly, is the formula? (Copy/Paste it here please).
--ron

I had the table embedded in a different sheet in the document. Moved it over
to the same sheet as the rest of my work and it worked. Thank you, Sir.
"Ron Rosenfeld" wrote:

On Sun, 24 Jan 2010 10:14:01 -0800, goskerz
wrote:

Sir, I have a very good feeling about this. I think it's going to work, just
having a little bit of trouble... For the conditional formatting formulas,
where do I type those in? I went to manage rules, new rule then "use a
formula to determine which cells to format" I copied your first formula in
there but recieved an error message. I am running office 2007. I wish I was a
little smarter on Excel, but I'm most likely doing something wrong. Thank you
for your help!

Having 2007 makes things a bit easier.

What was the error message?

Did you properly NAME the lookup table?

What, exactly, is the formula? (Copy/Paste it here please).
--ron
.

On Wed, 27 Jan 2010 13:45:01 -0800, goskerz
wrote:

I had the table embedded in a different sheet in the document. Moved it over
to the same sheet as the rest of my work and it worked. Thank you, Sir.

Yup. That is one of the limitations of the formulas used for conditional
formatting -- they must refer to the same sheet (there are other limitations,
too).

Glad you've got it working. Thanks for the feedback.
--ron

Ron

You can refer to another sheet for CF if you define a name for cell on other
sheet.

i.e. defined name "apple" refers to Sheet2A1

On Sheet1 in B2 CFFormula is: =B2<apple

Also can use use other workbooks if in VBE you add reference to other
workbook.

I have many UDF's in an add-in.

I set my workbook to reference the add-in and I can use UDF's from that
add-in in my CF formulas.


Gord


On Wed, 27 Jan 2010 21:51:01 -0500, Ron Rosenfeld
wrote:

On Wed, 27 Jan 2010 13:45:01 -0800, goskerz
wrote:

I had the table embedded in a different sheet in the document. Moved it over
to the same sheet as the rest of my work and it worked. Thank you, Sir.

Yup. That is one of the limitations of the formulas used for conditional
formatting -- they must refer to the same sheet (there are other limitations,
too).

Glad you've got it working. Thanks for the feedback.
--ron

On Thu, 28 Jan 2010 11:10:17 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

Ron

You can refer to another sheet for CF if you define a name for cell on other
sheet.

i.e. defined name "apple" refers to Sheet2A1

On Sheet1 in B2 CFFormula is: =B2<apple

I wonder why the OP had different results when he moved the table to the same
sheet. Has this always worked this way?



Also can use use other workbooks if in VBE you add reference to other
workbook.

I have many UDF's in an add-in.

I set my workbook to reference the add-in and I can use UDF's from that
add-in in my CF formulas.

Yes, I do that too and I have all of my custom functions and macros in an
add-in.
--ron

As far as I know using a defined name from another sheet has always been
allowed in CF

I have not been following the thread so don't know what OP had originally
set up or what problem got cleared up by moving a table.


Gord

On Thu, 28 Jan 2010 15:08:48 -0500, Ron Rosenfeld
wrote:

I wonder why the OP had different results when he moved the table to the same
sheet. Has this always worked this way?

On Thu, 28 Jan 2010 12:34:33 -0800, Gord Dibben <gorddibbATshawDOTca wrote:

As far as I know using a defined name from another sheet has always been
allowed in CF

I have not been following the thread so don't know what OP had originally
set up or what problem got cleared up by moving a table.


Gord

On Thu, 28 Jan 2010 15:08:48 -0500, Ron Rosenfeld
wrote:

I wonder why the OP had different results when he moved the table to the same
sheet. Has this always worked this way?

What he wrote was that he received an error message (otherwise unspecified)
initially. When I asked some questions to try to figure out the problem, he
wrote that the problem was solved by moving a lookup table used in the
conditional formatting formula from a different sheet to the same sheet.
--ron