Lookup macro

G

Guest

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, I’m 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
 
G

Guest

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
 
G

Guest

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
 
G

Guest

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 Data>Pivot 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

rota compile 1
VLOOKUP & IF used with > or < 7
which one to use: Macros / Formula's / Lookup 4
lookup for latest data 8
Rotating duty roster 8
Lookup 1
Fill Series for Lookup 2
Vlookup formula copy 4

Top