Scheduling workbook

  • Thread starter Thread starter Helder Andrade
  • Start date Start date
H

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
 
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?
Click to expand...

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
 
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
 
Helder Andrade said:
For the chart, Can I create a from and extract the data from the sheet?
Click to expand...

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
 
Back
Top