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