Hi
-- I have a workbook with 2 worksheets. The first sheet is a roster showing
employees scheduled duties which is compiled via a Vlookup list. This changes
taking into account the date.
The 2nd is the weekly allocation sheet which is based on the duty roster.
Sheet 1 A1 is the w/c date.
A4:A8 are employees names and B4:H8 are duties for each employee including
their rest day, Saturday to Friday.
Sheet2 A1 is the w/c date
A4:A7 are duties to be covered.
B4:H7 is name of employee covering the duty Saturday to Friday.
What I need to do is to populate sheet2 B4:H7 from sheet1 B4:H8 ignoring
people resting. A simple lookup formula does not appear to work, Im not sure
whether this is due to the fact the list of duties in sheet 1 varies
according to date and is not therefore in ascending order or the fact that it
is in itself populated from a vlookup formula in the first place.
Any help you can give with a macro to achieve this would be really
appreciated.

Al

Gramps;

I am assuming that you meant that your employees and workdates in equivelant
columns, not opposing as you stated. If you know the employees day off, why
not just use that as your qualifier.

=if(sheet1a1="Rest","Rest","Work")

In the employee name cell you select the cell with the above formula

=if(a1="Rest",whatever you want to say,sheet1b1)

Drag both statements down the column. Easy Peasy.

God Bless

Frank Pytel

"gramps" wrote:

Hi
-- I have a workbook with 2 worksheets. The first sheet is a roster showing
employees scheduled duties which is compiled via a Vlookup list. This changes
taking into account the date.
The 2nd is the weekly allocation sheet which is based on the duty roster.
Sheet 1 A1 is the w/c date.
A4:A8 are employees names and B4:H8 are duties for each employee including
their rest day, Saturday to Friday.
Sheet2 A1 is the w/c date
A4:A7 are duties to be covered.
B4:H7 is name of employee covering the duty Saturday to Friday.
What I need to do is to populate sheet2 B4:H7 from sheet1 B4:H8 ignoring
people resting. A simple lookup formula does not appear to work, Im not sure
whether this is due to the fact the list of duties in sheet 1 varies
according to date and is not therefore in ascending order or the fact that it
is in itself populated from a vlookup formula in the first place.
Any help you can give with a macro to achieve this would be really
appreciated.

Al

Hi Frank
I didn't explain clearly what I'm after & so below are the tables concerned
and what I want to do.
Sheet1
w/c sat sun mon tue wed thu fri
1/9 2/9 3/9 4/9 5/9 6/9 7/9
Alan A2 A2 R B1 B1 B1 B1
BILL R R A2 A2 A2 A2 A2
TOM R R A1 A1 A1 A1 A1
JOE B1 R B2 B2 B2 R B1
JIM A1 A1 B1 R R B2 B2

Sheet2
w/c sat sun mon tue wed thu fri
Duty 1/9 2/9 3/9 4/9 5/9 6/9 7/9
A1
A2
B1
B2

What I need is to fill the names against duties bearing in mind thatnot all
the duties are covered on Sat & Sun. I hope this claifies exactly what I'm
after.
Many Thanx

--
Al


"Frank Pytel" wrote:

Gramps;

I am assuming that you meant that your employees and workdates in equivelant
columns, not opposing as you stated. If you know the employees day off, why
not just use that as your qualifier.

=if(sheet1a1="Rest","Rest","Work")

In the employee name cell you select the cell with the above formula

=if(a1="Rest",whatever you want to say,sheet1b1)

Drag both statements down the column. Easy Peasy.

God Bless

Frank Pytel

"gramps" wrote:

Hi
-- I have a workbook with 2 worksheets. The first sheet is a roster showing
employees scheduled duties which is compiled via a Vlookup list. This changes
taking into account the date.
The 2nd is the weekly allocation sheet which is based on the duty roster.
Sheet 1 A1 is the w/c date.
A4:A8 are employees names and B4:H8 are duties for each employee including
their rest day, Saturday to Friday.
Sheet2 A1 is the w/c date
A4:A7 are duties to be covered.
B4:H7 is name of employee covering the duty Saturday to Friday.
What I need to do is to populate sheet2 B4:H7 from sheet1 B4:H8 ignoring
people resting. A simple lookup formula does not appear to work, Im not sure
whether this is due to the fact the list of duties in sheet 1 varies
according to date and is not therefore in ascending order or the fact that it
is in itself populated from a vlookup formula in the first place.
Any help you can give with a macro to achieve this would be really
appreciated.

Al

gramps;

I am wrong, I misread your original post. The visual clarifies it for me
dramatically. I would suggest putting your original information into a pivot
table.

Highlight your data and go to DataPivot Table. When the dialog box opens
click finish. This should allow you to place the dates at the top and the
tasks on the left. This should be what you're looking for.

God Bless

Frank Pytel
"gramps" wrote:

Hi Frank
I didn't explain clearly what I'm after & so below are the tables concerned
and what I want to do.
Sheet1
w/c sat sun mon tue wed thu fri
1/9 2/9 3/9 4/9 5/9 6/9 7/9
Alan A2 A2 R B1 B1 B1 B1
BILL R R A2 A2 A2 A2 A2
TOM R R A1 A1 A1 A1 A1
JOE B1 R B2 B2 B2 R B1
JIM A1 A1 B1 R R B2 B2

Sheet2
w/c sat sun mon tue wed thu fri
Duty 1/9 2/9 3/9 4/9 5/9 6/9 7/9
A1
A2
B1
B2

What I need is to fill the names against duties bearing in mind thatnot all
the duties are covered on Sat & Sun. I hope this claifies exactly what I'm
after.
Many Thanx

--
Al


"Frank Pytel" wrote:

Gramps;

I am assuming that you meant that your employees and workdates in equivelant
columns, not opposing as you stated. If you know the employees day off, why
not just use that as your qualifier.

=if(sheet1a1="Rest","Rest","Work")

In the employee name cell you select the cell with the above formula

=if(a1="Rest",whatever you want to say,sheet1b1)

Drag both statements down the column. Easy Peasy.

God Bless

Frank Pytel

"gramps" wrote:

Hi
-- I have a workbook with 2 worksheets. The first sheet is a roster showing
employees scheduled duties which is compiled via a Vlookup list. This changes
taking into account the date.
The 2nd is the weekly allocation sheet which is based on the duty roster.
Sheet 1 A1 is the w/c date.
A4:A8 are employees names and B4:H8 are duties for each employee including
their rest day, Saturday to Friday.
Sheet2 A1 is the w/c date
A4:A7 are duties to be covered.
B4:H7 is name of employee covering the duty Saturday to Friday.
What I need to do is to populate sheet2 B4:H7 from sheet1 B4:H8 ignoring
people resting. A simple lookup formula does not appear to work, Im not sure
whether this is due to the fact the list of duties in sheet 1 varies
according to date and is not therefore in ascending order or the fact that it
is in itself populated from a vlookup formula in the first place.
Any help you can give with a macro to achieve this would be really
appreciated.

Al