Scheduling workbook
 

Hello all,

I have been asked to help come up with a simple workbook to help with
scheduling my section at work. Here is what I got so far

Sheet One = Schedule
with the following columns

<WeekDay<Date<Employee<Hours<Location<Week<T ype<Assignment<ShiftID

Sheet Two = Time Off
with the following columns

<Employee<Date<AbsenceType<Approved<ApprovedBy

Here's what I'm trying to do.

1) When a Employee is entered into sheet one, check sheet to for time off,

if there is time off text colour red.

would this be pssible and what method should I be looking at?

2) Print a chart. Filtering <week in sheet one, print a chart for one
week with <employee on left side, <WeekDay across the top, and Hours in
the chart?

any help would be appreciated.



Helder

Scheduling workbook
 

Conditional formatting will not work with multiple sheets so in J2 to the
right of your data in Sheet1 enter the formula:

=SUMPRODUCT((Sheet2!$B$2:$B$31=B2)*(Sheet2!$A$2:$A $31=C2))

Change Sheet2!$B$2:$B$31 to larger than the maximum range that you ever will
use, (Sumproduct() will not work with whole columns).

You can hide Column J if you wish.

In the data area of Sheet 1 enter the formula in Conditional Formatting:

=$J2=1

and select to Format the font to RED.

For the chart:

2) Print a chart. Filtering <week in sheet one, print a chart for one
week with <employee on left side, <WeekDay across the top, and Hours in
the chart?

Charts don't work that way. If you want hours graphed then you need the
scale for hours values on the left-hand axis.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Helder Andrade" wrote in message
...
Hello all,

I have been asked to help come up with a simple workbook to help with
scheduling my section at work. Here is what I got so far

Sheet One = Schedule
with the following columns

<WeekDay<Date<Employee<Hours<Location<Week<T ype<Assignment<ShiftID

Sheet Two = Time Off
with the following columns

<Employee<Date<AbsenceType<Approved<ApprovedBy

Here's what I'm trying to do.

1) When a Employee is entered into sheet one, check sheet to for time off,

if there is time off text colour red.

would this be pssible and what method should I be looking at?

2) Print a chart. Filtering <week in sheet one, print a chart for one
week with <employee on left side, <WeekDay across the top, and Hours in
the chart?

any help would be appreciated.



Helder

Scheduling workbook
 

Thank you for the reply.

I will give this a try.

For the chart, Can I create a from and extract the data from the sheet?

Thanks again.
Helder


"Sandy Mann" wrote in message
...
Conditional formatting will not work with multiple sheets so in J2 to the
right of your data in Sheet1 enter the formula:

=SUMPRODUCT((Sheet2!$B$2:$B$31=B2)*(Sheet2!$A$2:$A $31=C2))

Change Sheet2!$B$2:$B$31 to larger than the maximum range that you ever
will use, (Sumproduct() will not work with whole columns).

You can hide Column J if you wish.

In the data area of Sheet 1 enter the formula in Conditional Formatting:

=$J2=1

and select to Format the font to RED.

For the chart:

2) Print a chart. Filtering <week in sheet one, print a chart for one
week with <employee on left side, <WeekDay across the top, and Hours in
the chart?

Charts don't work that way. If you want hours graphed then you need the
scale for hours values on the left-hand axis.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Helder Andrade" wrote in message
...
Hello all,

I have been asked to help come up with a simple workbook to help with
scheduling my section at work. Here is what I got so far

Sheet One = Schedule
with the following columns

<WeekDay<Date<Employee<Hours<Location<Week<T ype<Assignment<ShiftID

Sheet Two = Time Off
with the following columns

<Employee<Date<AbsenceType<Approved<ApprovedBy

Here's what I'm trying to do.

1) When a Employee is entered into sheet one, check sheet to for time
off,

if there is time off text colour red.

would this be pssible and what method should I be looking at?

2) Print a chart. Filtering <week in sheet one, print a chart for one
week with <employee on left side, <WeekDay across the top, and Hours in
the chart?

any help would be appreciated.



Helder

Scheduling workbook
 

"Helder Andrade" wrote in message
...
For the chart, Can I create a from and extract the data from the sheet?

I don't really follow what you are asking here but yes, you can extract data
from the sheet to a form.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk