several data colums into one personell rooster

[per.carlsson]

I'm trying to create a personell rooster chart with a 6 week cycle.
The workbook I have looks like this:

a: date
b: day
c:week
d:roosterweek
e: month
F: Name1
g: Name2
h: Name 3
i; Name4
Under each name is the times that individual should come to work


functions used
weekday()
Weeknr()
month()

For the rooster week i made a table of the week numbers and the tooster
weeks and used a hlookup function
weekno rooster week
1 1
2 2
3 3
4 4
5 5
6 6
7 1

What I want to do is to transform the table with the names and times
into a data list with names in one coloumn, times in one coloum and so
on.
I still want the raw data to be in the format as described above, but
if i make a change in times, i want that change to reproduce to the
whole year.
I want this in the data list format so my emplyees can use a pivot
table. That way the raw data is always protected and they cannot
accidentily screw it up.
Is there a smart way to do this?
I'm a new beginner to all this.

 

[KC Rippstein]

I think the lack of any response indicates that no one understands your
spreadsheet or what you are trying to accomplish with this personnel roster
(*note spelling of personnel roster*). Your design may need some more
thought, but I'll try to help with what you have and what you're after.

My best guess is:
- you have every weekday of the year listed from A2:A285 (just using 285 as
an example) and that holidays are included (so rows 2,7,12, etc. are always
Mondays);
- you only have 4 employees;
- they may have a set start time that differs each week but does follow a
six-week pattern, so if they start at 6 am on roster week 1, they always
start at 6 am every 6th week (which always equates back to roster week 1);
- when you make a change to their scheduled start time later in the year on
a particular week, you want that equivalent roster week's start time for
that employee to be updated for the rest of the year;
- I don't think you even need columns B:E, but I won't comment on that as
I'm sure you have your reasons. When you get to the end of my email here,
you may find you just want to delete columns B:E.

If this is remotely close to what you have set up, then here's some next
steps.
1) You need another 4 columns for overriding an employee's start time for a
given roster week. Col J would be Patrik New, etc.
2) Enter your start times for the employees in the first row in the new
columns you made (so J2:M2 should have start times for roster week 1 for all
4 employees). Anytime you physically enter a start time for someone, put
that new entry in their "New" column.
3) In F2, use this formula: =IF(J2="","",J2) and copy that formula across to
I2.
4) In F3, use this formula: =IF(J3="",IF(F2="","",F2),J3) and copy that
formula across to I3 and then down to row 6. Now Mon through Fri of week 1
has start times, and any single day can have a manual start time adjustment
that carries forward the rest of the week. If it's only supposed to be a
time adjustment for one day, just go to the next day and put the right times
back in again.
5) Now highlight F2:I6 and copy, then paste to F7, then paste to F12, until
all 6 weeks are accounted for. This should make F32 your next blank cell.
6) In F32, use this formula: =IF(J32="",IF(F6="","",F6),J32) and use the
fill handle to copy across to I32. Now copy F28:I31 and paste to F33.
7) Now select the range F32:I36, copy it and then paste it to F37, then
paste it to F42, and so on to the end of your list.
8) Set up a new sheet with the data repeated, but it sounds like you just
need the date from Col A and then the times from Col F:I. So in A2,
=Sheet1!A2, B2 =Sheet1!F2, C2 =Sheet1!G2, D2 =Sheet1!H2, and E2 =Sheet1!I2.
Now select the range A2:E2 and use the fill handle to drag down to the end
of your list (row 285?). Now you will have the original list with just the
dates and start times.

I hope this is what you were after. It was really hard to understand your
design and your intent, and I think you'll find you don't need columns B
through E on your initial sheet when you're done.
- KC

 

[Oslopelle]

Thanks for trying to make sense of my problem!
Let me try to explain it somewhat.

Yes, the design is quite complex.
It would be so much easier if there was a grid to show it in.
However.

I have 50 employees
in coloumn a i have dates
in column b weeks
in column c
column d indicates which week of the rotation it is.
column e is month
These columns might or might not be essential for the rest of the
project


In in f3 is the name of the first emplyee, Patrik, g3 contains the
name of my second emplyee Gullan and so on.
Below these names their work times are set in tekst format for monday
week 1, 8-16 in f4 for Patrik, 12-19 in g4 for Gullan. For tuesday
week 1 in the rotation, in f5, 9-17 for patrik and 15-23 in g5 for
Gullan.

These 6 weeks are the basis for the whole year plan. A change in f4
would change times on all mondays of roster week 1 the entire year for
Patrik.

These 6 basis weeks should rotate a whole year for all my emplyees.

To get a whole year plan in a format leaves my raw data protected so
my emplyees cannot change or edit them, i thought that it would be nice
to show the schedule in the form of a pivot table.

To get it into such a table i have to have a data list and the previous
design is not a data list since it is based on both columns and a row
for the names (or am i mistaken, complete novice to pivot tables)

To get the data form the format described above into a data list that
are based on the whole year for each of my emplyees are the tricky bit.
And get that data list to update if i change anything in the raw data
of the basis weeks.

Is there a smart way to turn my initial design into a normal data list
in the format of
date week roster-week month time name
01.01.07 1 1 1 8-16 Patrik
01.01.07 1 1 1 12-19 Gullan
and so on?
And to get said list to update if i change the raw data of the basis 6
weeks?

maybe a combination of lookup functions? But I have absolutely no
experience with lookups...




KC Rippstein skrev:

 

[KC Rippstein]

So it sounds like I was close...the only thing I got wrong was that people
can have different start times Mon-Fri within a week.

In that case, what I told you before still works, the only changes to make
are:
- when you finish step 3, drag that formula down to the end of that week
and skip step 4
- when you finish the first part of step 6, just drag that formula down the
rest of the page and skip step 7

I think you may want to set up your "report" in a separate workbook so other
people have no access to the raw data. Also, I do not follow what you want
the pivot table to do for you. It can show dates down the side and
employees across the top with no trouble, but it will not fill in their
start times for each day in the data area. It will just give you a "1" if
they work that day or "0"/blank if they don't work that day.

You should probably just set up your other worksheet (or workbook) to look
like the report you want, with names across the top, dates down column A,
and times in the data area. Lookup formulas would not even be necessary,
it's just as simple as B2 on the report = F2 from your data (that's Patrik
for 1/1/07), then use the fill handle to drag that formula across to all
employees and down for the whole year.

Feel free to email me your file if this is getting too confusing. I still
think your design is a little inflexible. What are you going to do when you
have turnover? Are you just going to put the new person's name in the old
person's slot and continue the year as if nothing changed?

- KC