is this possible on excel?

  • Thread starter Thread starter DR, Bob
  • Start date Start date
D

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?
 
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
 
thanks I'll give that a try.
Max said:
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
Click to expand...
 
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
Click to expand...


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.
 
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
 
Back
Top