is this possible on excel?

[DR, Bob]

I use a spread sheet to do work schedual
12 work sheet 1 for each month
columes are total days for the whole month( i use 4 extra columes between
weeks for extra info.)
rows are name of staff
I want to add 1 more work sheet to keep track of vacation time
Is there a formula or way so it will automatically mark on the schedual the
vacation time as entered in the vacation work sheet on the proper month and
day?

 

[Max]

Maybe something along the lines of this set-up ..

Assume you have in Sheet1, in cols A to D,
data in row2 downwards, the set-up:

Name...........Reason....1st day of abs...Last day of abs
John Walt.......MC..........01-Jul-04..........01-Jul-04
Peter Logan.....V............02-Jul-04..........03-Jul-04

where details concerned for staff on vacation, on sick leave etc
(e.g. "V", "MC" etc in col B) are entered

Notes:
----------
a. The names in col A in Sheet1 are assumed *unique*,
i.e. appear maximum once only

b. For *single day* events, the same date has to be entered
in *both* cols C & D
(Like what was entered for "John Walt" in the sample above)

-------------
In Sheet2 (for month of Jul-04, say)
-------------
Suppose you have the typical set-up below, in cols A - col xx,
data in row2 downwards, where:

- Names are listed in col A, row2 down
(The names need not necessarily be in the same order as Sheet1)

- Row1 in cols B, C, D, etc contain all the *dates* for the entire month
in sequential order right across, viz.: 1-Jul-04, 2-Jul-04 .. 31-Jul-04
(Note: Your 4 extra cols in-between each week shouldn't be a problem
as long as the col headers for these in row1 doesn't contain any *dates*,
which might conflict)

Name...........1-Jul-04...2-Jul-04...3-Jul-04...etc
Peter Logan
John Walt
etc

Put in B2:

=IF(ISBLANK($A2),"",IF(ISNA(MATCH(TRIM($A2),Sheet1!$A:$A,0)),"Unmatched
Name",IF(OR(B$1<OFFSET(Sheet1!$A$1,MATCH(TRIM($A2),Sheet1!$A:$A,0)-1,2),B$1>
OFFSET(Sheet1!$A$1,MATCH(TRIM($A2),Sheet1!$A:$A,0)-1,3)),"",OFFSET(Sheet1!$A
$1,MATCH(TRIM($A2),Sheet1!$A:$A,0)-1,1))))

Copy B2 right across until the rightmost col (for the last day of the
month),
then copy down until the last row of data in col A

The desired results for each name in col A will be returned by the formulae,
viz. for the sample data in Sheet1, Sheet2 will return:

Name...........01-Jul-04 02-Jul-04 03-Jul-04
Peter Logan........................V..............V
John Walt..........MC

Blank cells in col A (if any) will return blanks, while
"Unmatched Name" will be returned for cases
where the names in col A do not match those in Sheet1's col A

TRIM() is used to improve robustness in matching the names,
to remove any inadvertent leading, trailing or extra in-between-words spaces
in the names entered in col A of Sheet2

 

[DR, Bob]

thanks I'll give that a try.

Maybe something along the lines of this set-up ..

Assume you have in Sheet1, in cols A to D,
data in row2 downwards, the set-up:

Name...........Reason....1st day of abs...Last day of abs
John Walt.......MC..........01-Jul-04..........01-Jul-04
Peter Logan.....V............02-Jul-04..........03-Jul-04

where details concerned for staff on vacation, on sick leave etc
(e.g. "V", "MC" etc in col B) are entered

Notes:
----------
a. The names in col A in Sheet1 are assumed *unique*,
i.e. appear maximum once only

b. For *single day* events, the same date has to be entered
in *both* cols C & D
(Like what was entered for "John Walt" in the sample above)

-------------
In Sheet2 (for month of Jul-04, say)
-------------
Suppose you have the typical set-up below, in cols A - col xx,
data in row2 downwards, where:

- Names are listed in col A, row2 down
(The names need not necessarily be in the same order as Sheet1)

- Row1 in cols B, C, D, etc contain all the *dates* for the entire month
in sequential order right across, viz.: 1-Jul-04, 2-Jul-04 .. 31-Jul-04
(Note: Your 4 extra cols in-between each week shouldn't be a problem
as long as the col headers for these in row1 doesn't contain any *dates*,
which might conflict)

Name...........1-Jul-04...2-Jul-04...3-Jul-04...etc
Peter Logan
John Walt
etc

Put in B2:

=IF(ISBLANK($A2),"",IF(ISNA(MATCH(TRIM($A2),Sheet1!$A:$A,0)),"Unmatched

OFFSET(Sheet1!$A$1,MATCH(TRIM($A2),Sheet1!$A:$A,0)-1,3)),"",OFFSET(Sheet1!$A
$1,MATCH(TRIM($A2),Sheet1!$A:$A,0)-1,1))))

Copy B2 right across until the rightmost col (for the last day of the
month),
then copy down until the last row of data in col A

The desired results for each name in col A will be returned by the formulae,
viz. for the sample data in Sheet1, Sheet2 will return:

Name...........01-Jul-04 02-Jul-04 03-Jul-04
Peter Logan........................V..............V
John Walt..........MC

Blank cells in col A (if any) will return blanks, while
"Unmatched Name" will be returned for cases
where the names in col A do not match those in Sheet1's col A

TRIM() is used to improve robustness in matching the names,
to remove any inadvertent leading, trailing or extra in-between-words spaces
in the names entered in col A of Sheet2

 

[Max]

Pleasure' Bob !
Hope it works for you ..

 

[DR, Bob]

The formula will work to fit my exact needs but a modification s needed if
possible.
On sheet 1 I built it as your example.

Assume you have in Sheet1, in cols A to D,
data in row2 downwards, the set-up:
Name...........Reason....1st day of abs...Last day of abs
John Walt.......MC..........01-Jul-04..........01-Jul-04
Peter Logan.....V............02-Jul-04..........03-Jul-04

I need to enter in row 2 for example John Walt his first day off (reason in
Column B dates in columns C&D, 2nd day(s) off in row 2 reason Column E dates
columns F & G and keep repeating to meet the number of days off one my have
in a month. As I am useing this on Vacation days some of my staff have up to
6 weeks holidays which may be broken into several different days in a month.
ie may take off July 3 to 6, then july 15 to20 & July 29.

 

[Max]

Sorry, Bob .. guess I'm out of ideas to modify as posted, but
perhaps you might want to try this alternative approach instead:

Suppose we now have

In Sheet1 (for input of staff on vacation, sick, etc)
------------
Names listed in row1, B1 across
with dates listed in col A, A2 down

Date.........John Walt..Peter Logan..etc
01-Jul-04...V
02-Jul-04...................MC
03-Jul-04...V
04-Jul-04...V..............V
etc

where V = On vacation, MC = On sick leave, etc

In Sheet2
-------------
Assuming the same headers (dates*) in row1, B1 across
and names listed in col A, A2 down
*may include your extra in-between cols, as before

Name...........1-Jul-04...2-Jul-04...3-Jul-04...etc
Peter Logan
John Walt
etc

Put in B2:

=IF(OR(ISNA(MATCH(B$1,Sheet1!$A:$A,0)),ISNA(MATCH($A2,Sheet1!$1:$1,0))),"",O
FFSET(Sheet1!$A$1,MATCH(B$1,Sheet1!$A:$A,0)-1,MATCH($A2,Sheet1!$1:$1,0)-1))

Copy B2 across until the rightmost col
(for the last day of the month),
then copy down until the last row of data in col A

For a neat look, we'll suppress extraneous zeros
from showing in Sheet2 via clicking:
Tools > Options > View tab > Uncheck "Zero values" > OK

The above will now return:

Name...........01-Jul-04 02-Jul-04 03-Jul-04 04-Jul-04
Peter Logan........................MC........................V
John Walt..........V............................V.............V
etc