autofill colour based on date entry

I have a spreadsheet to record when monthly duty reports are received by each
employee and reviewed by mgmt.

Column A1:A200 Employee Name
Column B1:B200 Date Received in April
Column C1:C200 Date Reviewed in April
Column D1200 Date Received in May
Column E1:E200 Date Reviewed in May etc

Is there a formula I could use to fill the cell in grey when a report is not
received
when a report is received late in blue and when a report is received on time
in pink?

Reports received the 5th business day of the following month of the report
(i.e. april report due the 5th business day in may) are considered late and
reports reviewed after the 10th business day are late.

Any suggestions without using code?


Thanks!!

you would use the conditional formatting feature, along with the networkdays
formula, which is in the analysis toolpac add-in (if you don't have it,
tools/add-ins and check it)

"Tacrier" wrote:

I have a spreadsheet to record when monthly duty reports are received by each
employee and reviewed by mgmt.

Column A1:A200 Employee Name
Column B1:B200 Date Received in April
Column C1:C200 Date Reviewed in April
Column D1200 Date Received in May
Column E1:E200 Date Reviewed in May etc

Is there a formula I could use to fill the cell in grey when a report is not
received
when a report is received late in blue and when a report is received on time
in pink?

Reports received the 5th business day of the following month of the report
(i.e. april report due the 5th business day in may) are considered late and
reports reviewed after the 10th business day are late.

Any suggestions without using code?


Thanks!!

Unfortunately I am using Excel 2000 and in the process of getting my tech
guys to buy the 2007 office suite for my department.

Any other suggestions?

"dlw" wrote:

you would use the conditional formatting feature, along with the networkdays
formula, which is in the analysis toolpac add-in (if you don't have it,
tools/add-ins and check it)

"Tacrier" wrote:

I have a spreadsheet to record when monthly duty reports are received by each
employee and reviewed by mgmt.

Column A1:A200 Employee Name
Column B1:B200 Date Received in April
Column C1:C200 Date Reviewed in April
Column D1200 Date Received in May
Column E1:E200 Date Reviewed in May etc

Is there a formula I could use to fill the cell in grey when a report is not
received
when a report is received late in blue and when a report is received on time
in pink?

Reports received the 5th business day of the following month of the report
(i.e. april report due the 5th business day in may) are considered late and
reports reviewed after the 10th business day are late.

Any suggestions without using code?


Thanks!!

I have excel 2000, and it has the analysis toolpac. Maybe I downloaded it?
Check the MS web site.

"Tacrier" wrote:

Unfortunately I am using Excel 2000 and in the process of getting my tech
guys to buy the 2007 office suite for my department.

Any other suggestions?

"dlw" wrote:

you would use the conditional formatting feature, along with the networkdays
formula, which is in the analysis toolpac add-in (if you don't have it,
tools/add-ins and check it)

"Tacrier" wrote:

I have a spreadsheet to record when monthly duty reports are received by each
employee and reviewed by mgmt.

Column A1:A200 Employee Name
Column B1:B200 Date Received in April
Column C1:C200 Date Reviewed in April
Column D1200 Date Received in May
Column E1:E200 Date Reviewed in May etc

Is there a formula I could use to fill the cell in grey when a report is not
received
when a report is received late in blue and when a report is received on time
in pink?

Reports received the 5th business day of the following month of the report
(i.e. april report due the 5th business day in may) are considered late and
reports reviewed after the 10th business day are late.

Any suggestions without using code?


Thanks!!

The ATP cannot be downloaded.

It is installed upon a "typical" installation in versions earlier than 2007.

Activate through ToolsAdd-insATP


Gord Dibben MS Excel MVP

On Fri, 5 Dec 2008 07:53:00 -0800, dlw
wrote:

I have excel 2000, and it has the analysis toolpac. Maybe I downloaded it?
Check the MS web site.

"Tacrier" wrote:

Unfortunately I am using Excel 2000 and in the process of getting my tech
guys to buy the 2007 office suite for my department.

Any other suggestions?

"dlw" wrote:

you would use the conditional formatting feature, along with the networkdays
formula, which is in the analysis toolpac add-in (if you don't have it,
tools/add-ins and check it)

"Tacrier" wrote:

I have a spreadsheet to record when monthly duty reports are received by each
employee and reviewed by mgmt.

Column A1:A200 Employee Name
Column B1:B200 Date Received in April
Column C1:C200 Date Reviewed in April
Column D1200 Date Received in May
Column E1:E200 Date Reviewed in May etc

Is there a formula I could use to fill the cell in grey when a report is not
received
when a report is received late in blue and when a report is received on time
in pink?

Reports received the 5th business day of the following month of the report
(i.e. april report due the 5th business day in may) are considered late and
reports reviewed after the 10th business day are late.

Any suggestions without using code?


Thanks!!

I tried to activate it through the toolsadd-insATP and a message appears
indicating a CD-ROM needs to be inserted. Still no luck.

"Gord Dibben" wrote:

The ATP cannot be downloaded.

It is installed upon a "typical" installation in versions earlier than 2007.

Activate through ToolsAdd-insATP


Gord Dibben MS Excel MVP

On Fri, 5 Dec 2008 07:53:00 -0800, dlw
wrote:

I have excel 2000, and it has the analysis toolpac. Maybe I downloaded it?
Check the MS web site.

"Tacrier" wrote:

Unfortunately I am using Excel 2000 and in the process of getting my tech
guys to buy the 2007 office suite for my department.

Any other suggestions?

"dlw" wrote:

you would use the conditional formatting feature, along with the networkdays
formula, which is in the analysis toolpac add-in (if you don't have it,
tools/add-ins and check it)

"Tacrier" wrote:

I have a spreadsheet to record when monthly duty reports are received by each
employee and reviewed by mgmt.

Column A1:A200 Employee Name
Column B1:B200 Date Received in April
Column C1:C200 Date Reviewed in April
Column D1200 Date Received in May
Column E1:E200 Date Reviewed in May etc

Is there a formula I could use to fill the cell in grey when a report is not
received
when a report is received late in blue and when a report is received on time
in pink?

Reports received the 5th business day of the following month of the report
(i.e. april report due the 5th business day in may) are considered late and
reports reviewed after the 10th business day are late.

Any suggestions without using code?


Thanks!!

The ATP is available under ToolsAdd-ins?

You have it checked?

Try unchecking, close dialog then ToolsAdd-ins and re-check it.

If not available have a look in the default install folder.

C:\Program Files\Microsoft Office\Office9\Library\Analysis

There will be 4 files in the Analysis folder.

ANALYS32.XLL
FUNCRES.XLA
ATPVBAEN.XLA
PROCDB.XLA

If not found there, suggest you try the control panel add/removechange and
go in and remove the ATP installation then re-install just the ATP

Make sure you set it "run all from my computer"

Assuming you have the installation CD, of course.


Gord

On Mon, 8 Dec 2008 09:26:06 -0800, Tacrier
. wrote:

I tried to activate it through the toolsadd-insATP and a message appears
indicating a CD-ROM needs to be inserted. Still no luck.

"Gord Dibben" wrote:

The ATP cannot be downloaded.

It is installed upon a "typical" installation in versions earlier than 2007.

Activate through ToolsAdd-insATP


Gord Dibben MS Excel MVP

On Fri, 5 Dec 2008 07:53:00 -0800, dlw
wrote:

I have excel 2000, and it has the analysis toolpac. Maybe I downloaded it?
Check the MS web site.

"Tacrier" wrote:

Unfortunately I am using Excel 2000 and in the process of getting my tech
guys to buy the 2007 office suite for my department.

Any other suggestions?

"dlw" wrote:

you would use the conditional formatting feature, along with the networkdays
formula, which is in the analysis toolpac add-in (if you don't have it,
tools/add-ins and check it)

"Tacrier" wrote:

I have a spreadsheet to record when monthly duty reports are received by each
employee and reviewed by mgmt.

Column A1:A200 Employee Name
Column B1:B200 Date Received in April
Column C1:C200 Date Reviewed in April
Column D1200 Date Received in May
Column E1:E200 Date Reviewed in May etc

Is there a formula I could use to fill the cell in grey when a report is not
received
when a report is received late in blue and when a report is received on time
in pink?

Reports received the 5th business day of the following month of the report
(i.e. april report due the 5th business day in may) are considered late and
reports reviewed after the 10th business day are late.

Any suggestions without using code?


Thanks!!

I do not have the original installation CD

Will check back with all of your suggestions once I am able to install the
ATP.

Thanks again

Trina C

"Gord Dibben" wrote:

The ATP is available under ToolsAdd-ins?

You have it checked?

Try unchecking, close dialog then ToolsAdd-ins and re-check it.

If not available have a look in the default install folder.

C:\Program Files\Microsoft Office\Office9\Library\Analysis

There will be 4 files in the Analysis folder.

ANALYS32.XLL
FUNCRES.XLA
ATPVBAEN.XLA
PROCDB.XLA

If not found there, suggest you try the control panel add/removechange and
go in and remove the ATP installation then re-install just the ATP

Make sure you set it "run all from my computer"

Assuming you have the installation CD, of course.


Gord

On Mon, 8 Dec 2008 09:26:06 -0800, Tacrier
. wrote:

I tried to activate it through the toolsadd-insATP and a message appears
indicating a CD-ROM needs to be inserted. Still no luck.

"Gord Dibben" wrote:

The ATP cannot be downloaded.

It is installed upon a "typical" installation in versions earlier than 2007.

Activate through ToolsAdd-insATP


Gord Dibben MS Excel MVP

On Fri, 5 Dec 2008 07:53:00 -0800, dlw
wrote:

I have excel 2000, and it has the analysis toolpac. Maybe I downloaded it?
Check the MS web site.

"Tacrier" wrote:

Unfortunately I am using Excel 2000 and in the process of getting my tech
guys to buy the 2007 office suite for my department.

Any other suggestions?

"dlw" wrote:

you would use the conditional formatting feature, along with the networkdays
formula, which is in the analysis toolpac add-in (if you don't have it,
tools/add-ins and check it)

"Tacrier" wrote:

I have a spreadsheet to record when monthly duty reports are received by each
employee and reviewed by mgmt.

Column A1:A200 Employee Name
Column B1:B200 Date Received in April
Column C1:C200 Date Reviewed in April
Column D1200 Date Received in May
Column E1:E200 Date Reviewed in May etc

Is there a formula I could use to fill the cell in grey when a report is not
received
when a report is received late in blue and when a report is received on time
in pink?

Reports received the 5th business day of the following month of the report
(i.e. april report due the 5th business day in may) are considered late and
reports reviewed after the 10th business day are late.

Any suggestions without using code?


Thanks!!

I now have the analysis toolpak, how do i go about using the networkdays
formula in the conditional formatting?


"Tacrier" wrote:

I do not have the original installation CD

Will check back with all of your suggestions once I am able to install the
ATP.

Thanks again

Trina C

"Gord Dibben" wrote:

The ATP is available under ToolsAdd-ins?

You have it checked?

Try unchecking, close dialog then ToolsAdd-ins and re-check it.

If not available have a look in the default install folder.

C:\Program Files\Microsoft Office\Office9\Library\Analysis

There will be 4 files in the Analysis folder.

ANALYS32.XLL
FUNCRES.XLA
ATPVBAEN.XLA
PROCDB.XLA

If not found there, suggest you try the control panel add/removechange and
go in and remove the ATP installation then re-install just the ATP

Make sure you set it "run all from my computer"

Assuming you have the installation CD, of course.


Gord

On Mon, 8 Dec 2008 09:26:06 -0800, Tacrier
. wrote:

I tried to activate it through the toolsadd-insATP and a message appears
indicating a CD-ROM needs to be inserted. Still no luck.

"Gord Dibben" wrote:

The ATP cannot be downloaded.

It is installed upon a "typical" installation in versions earlier than 2007.

Activate through ToolsAdd-insATP


Gord Dibben MS Excel MVP

On Fri, 5 Dec 2008 07:53:00 -0800, dlw
wrote:

I have excel 2000, and it has the analysis toolpac. Maybe I downloaded it?
Check the MS web site.

"Tacrier" wrote:

Unfortunately I am using Excel 2000 and in the process of getting my tech
guys to buy the 2007 office suite for my department.

Any other suggestions?

"dlw" wrote:

you would use the conditional formatting feature, along with the networkdays
formula, which is in the analysis toolpac add-in (if you don't have it,
tools/add-ins and check it)

"Tacrier" wrote:

I have a spreadsheet to record when monthly duty reports are received by each
employee and reviewed by mgmt.

Column A1:A200 Employee Name
Column B1:B200 Date Received in April
Column C1:C200 Date Reviewed in April
Column D1200 Date Received in May
Column E1:E200 Date Reviewed in May etc

Is there a formula I could use to fill the cell in grey when a report is not
received
when a report is received late in blue and when a report is received on time
in pink?

Reports received the 5th business day of the following month of the report
(i.e. april report due the 5th business day in may) are considered late and
reports reviewed after the 10th business day are late.

Any suggestions without using code?


Thanks!!

You can't do it directly.
You'd get a message like: "you may not use references to other worksheets or
workbooks for conditional formatting formulas".
CF regards the ATP as being another worksheet or workbook.
You'd have to use a helper column to do the necessary ATP calculation, the
use the result of that in your CF formula.
--
David Biddulph

"Tacrier" . wrote in message
news

I now have the analysis toolpak, how do i go about using the networkdays
formula in the conditional formatting?


"Tacrier" wrote:

I do not have the original installation CD

Will check back with all of your suggestions once I am able to install
the
ATP.

Thanks again

Trina C

"Gord Dibben" wrote:

The ATP is available under ToolsAdd-ins?

You have it checked?

Try unchecking, close dialog then ToolsAdd-ins and re-check it.

If not available have a look in the default install folder.

C:\Program Files\Microsoft Office\Office9\Library\Analysis

There will be 4 files in the Analysis folder.

ANALYS32.XLL
FUNCRES.XLA
ATPVBAEN.XLA
PROCDB.XLA

If not found there, suggest you try the control panel add/removechange
and
go in and remove the ATP installation then re-install just the ATP

Make sure you set it "run all from my computer"

Assuming you have the installation CD, of course.


Gord

On Mon, 8 Dec 2008 09:26:06 -0800, Tacrier
. wrote:

I tried to activate it through the toolsadd-insATP and a message
appears
indicating a CD-ROM needs to be inserted. Still no luck.

"Gord Dibben" wrote:

The ATP cannot be downloaded.

It is installed upon a "typical" installation in versions earlier
than 2007.

Activate through ToolsAdd-insATP


Gord Dibben MS Excel MVP

On Fri, 5 Dec 2008 07:53:00 -0800, dlw

wrote:

I have excel 2000, and it has the analysis toolpac. Maybe I
downloaded it?
Check the MS web site.

"Tacrier" wrote:

Unfortunately I am using Excel 2000 and in the process of getting
my tech
guys to buy the 2007 office suite for my department.

Any other suggestions?

"dlw" wrote:

you would use the conditional formatting feature, along with
the networkdays
formula, which is in the analysis toolpac add-in (if you don't
have it,
tools/add-ins and check it)

"Tacrier" wrote:

I have a spreadsheet to record when monthly duty reports are
received by each
employee and reviewed by mgmt.

Column A1:A200 Employee Name
Column B1:B200 Date Received in April
Column C1:C200 Date Reviewed in April
Column D1200 Date Received in May
Column E1:E200 Date Reviewed in May etc

Is there a formula I could use to fill the cell in grey when
a report is not
received
when a report is received late in blue and when a report is
received on time
in pink?

Reports received the 5th business day of the following month
of the report
(i.e. april report due the 5th business day in may) are
considered late and
reports reviewed after the 10th business day are late.

Any suggestions without using code?


Thanks!!