Hi

I have checked through the posts and there are similar questions but I still
can't seem to get it right.
I have a sheet which is updated weekly with day of week sick leave is taken
and the staff taking it in the below format:
A B
1 Mon Bob Smith
2 Mon Donald Duck
3 Tue Mickey Mouse

I have 7 other sheets in the workbook designated Mon-Sun. I am trying to
have the 2nd row in the Mon sheet return all names (in separate cells) who
have taken sick leave on a monday, however vlookup only gives me the first of
any given day. How can I set it up so I can retrieve all the names for a
given day? As the data is updated weekly, the array can vary so I have just
been trying to select the entire columns (A & B).

Cheers
Soph

Sorry to answer your question with a question ... but have you
thought of using a Pivot Table for this report?
I don't know what you do with the result of your lookups, but if you
just want to see who has taken sick days on particular days, the
Pivot Table will do it for you in an instant.
Rgds,
ScottO


"soph" wrote in message
...
| Hi
|
| I have checked through the posts and there are similar questions
but I still
| can't seem to get it right.
| I have a sheet which is updated weekly with day of week sick leave
is taken
| and the staff taking it in the below format:
| A B
| 1 Mon Bob Smith
| 2 Mon Donald Duck
| 3 Tue Mickey Mouse
|
| I have 7 other sheets in the workbook designated Mon-Sun. I am
trying to
| have the 2nd row in the Mon sheet return all names (in separate
cells) who
| have taken sick leave on a monday, however vlookup only gives me
the first of
| any given day. How can I set it up so I can retrieve all the names
for a
| given day? As the data is updated weekly, the array can vary so I
have just
| been trying to select the entire columns (A & B).
|
| Cheers
| Soph

Hey Scott

Unfortunately I need to then break down the leave into 15 min increments and
convert into a % of the total hours worked so I don't think a pivot will help
me with that

Thanks!
Soph

"ScottO" wrote:

Sorry to answer your question with a question ... but have you
thought of using a Pivot Table for this report?
I don't know what you do with the result of your lookups, but if you
just want to see who has taken sick days on particular days, the
Pivot Table will do it for you in an instant.
Rgds,
ScottO


"soph" wrote in message
...
| Hi
|
| I have checked through the posts and there are similar questions
but I still
| can't seem to get it right.
| I have a sheet which is updated weekly with day of week sick leave
is taken
| and the staff taking it in the below format:
| A B
| 1 Mon Bob Smith
| 2 Mon Donald Duck
| 3 Tue Mickey Mouse
|
| I have 7 other sheets in the workbook designated Mon-Sun. I am
trying to
| have the 2nd row in the Mon sheet return all names (in separate
cells) who
| have taken sick leave on a monday, however vlookup only gives me
the first of
| any given day. How can I set it up so I can retrieve all the names
for a
| given day? As the data is updated weekly, the array can vary so I
have just
| been trying to select the entire columns (A & B).
|
| Cheers
| Soph