Scheduling workbook

[Helder Andrade]

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><Type><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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Helder Andrade]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk