match
 

Hi,

I have 2 work books. One is weekly schedule and other one is yearly vacation
schedule. I would like to link both books matching names and dates. Vacation
schedule is in monthly format and weekly schedule is in weekly format.

Eg: yearly vacation schdule
jan 1 Jan 2 Jan 3 and so on
ram siva
ganga

weekly schedule
jan 1 Jan 2 Jan 3
ram off 10-6 9-5
ganga off 9-5 11-7
siva 10-6 off 9-5

I need a formula in weekly schedule saying that if the date and name match
in yearly schdule says €œoff€� otherwise shift time.

Thank you in advance

match
 

I ahve assumed your dates are in Year Sched.xls Sheet 1 row 1, and that
your weekly schedule has dates starting in B1, with names starting in A2.

In B2, enter

=IF(ISERROR(MATCH($A2,OFFSET('[Year
Sched.xls]Sheet1'!$A$1,0,MATCH(B$1,'[Year
Sched.xls]Sheet1'!$A$1:$G$1,FALSE)-1,6,1),FALSE)),"Schedule Formula","Off")

and copy down and across, first replacing "Schedule Formula" with a formula
that returns the schedule information for that worker, like

VLOOKUP($A2,WorkTable,2,False)

where WorkTable is a two column data table of names and schedules, like so:

VLOOKUP($A2,$I$2:$J$50,2,False)

So the final formula would be something like:

=IF(ISERROR(MATCH($A2,OFFSET('[Year
Sched.xls]Sheet1'!$A$1,0,MATCH(B$1,'[Year
Sched.xls]Sheet1'!$A$1:$G$1,FALSE)-1,6,1),FALSE)),VLOOKUP($A2,$I$2:$J$50,2,False),"Of f")

HTH,
Bernie
MS Excel MVP


"ganga" wrote in message
...
Hi,

I have 2 work books. One is weekly schedule and other one is yearly
vacation
schedule. I would like to link both books matching names and dates.
Vacation
schedule is in monthly format and weekly schedule is in weekly format.

Eg: yearly vacation schdule
jan 1 Jan 2 Jan 3 and so on
ram siva
ganga

weekly schedule
jan 1 Jan 2 Jan 3
ram off 10-6 9-5
ganga off 9-5 11-7
siva 10-6 off 9-5

I need a formula in weekly schedule saying that if the date and name match
in yearly schdule says "off" otherwise shift time.

Thank you in advance

match
 

Thank you Bernie. Its working but only problem is I don't have dates only in
row 1 . is it possible to get your email address so i can send you my
template cause its so much confusion to explain everything. My Yearly schdule
is basically a calender.

Thank you again

"Bernie Deitrick" wrote:

I ahve assumed your dates are in Year Sched.xls Sheet 1 row 1, and that
your weekly schedule has dates starting in B1, with names starting in A2.

In B2, enter

=IF(ISERROR(MATCH($A2,OFFSET('[Year
Sched.xls]Sheet1'!$A$1,0,MATCH(B$1,'[Year
Sched.xls]Sheet1'!$A$1:$G$1,FALSE)-1,6,1),FALSE)),"Schedule Formula","Off")

and copy down and across, first replacing "Schedule Formula" with a formula
that returns the schedule information for that worker, like

VLOOKUP($A2,WorkTable,2,False)

where WorkTable is a two column data table of names and schedules, like so:

VLOOKUP($A2,$I$2:$J$50,2,False)

So the final formula would be something like:

=IF(ISERROR(MATCH($A2,OFFSET('[Year
Sched.xls]Sheet1'!$A$1,0,MATCH(B$1,'[Year
Sched.xls]Sheet1'!$A$1:$G$1,FALSE)-1,6,1),FALSE)),VLOOKUP($A2,$I$2:$J$50,2,False),"Of f")

HTH,
Bernie
MS Excel MVP


"ganga" wrote in message
...
Hi,

I have 2 work books. One is weekly schedule and other one is yearly
vacation
schedule. I would like to link both books matching names and dates.
Vacation
schedule is in monthly format and weekly schedule is in weekly format.

Eg: yearly vacation schdule
jan 1 Jan 2 Jan 3 and so on
ram siva
ganga

weekly schedule
jan 1 Jan 2 Jan 3
ram off 10-6 9-5
ganga off 9-5 11-7
siva 10-6 off 9-5

I need a formula in weekly schedule saying that if the date and name match
in yearly schdule says "off" otherwise shift time.

Thank you in advance


.

match
 

Make the obvious changes to my email address

deitbe at consumer dot org.

Bernie


"ganga" wrote in message
...
Thank you Bernie. Its working but only problem is I don't have dates only
in
row 1 . is it possible to get your email address so i can send you my
template cause its so much confusion to explain everything. My Yearly
schdule
is basically a calender.

Thank you again

"Bernie Deitrick" wrote:

I ahve assumed your dates are in Year Sched.xls Sheet 1 row 1, and that
your weekly schedule has dates starting in B1, with names starting in A2.

In B2, enter

=IF(ISERROR(MATCH($A2,OFFSET('[Year
Sched.xls]Sheet1'!$A$1,0,MATCH(B$1,'[Year
Sched.xls]Sheet1'!$A$1:$G$1,FALSE)-1,6,1),FALSE)),"Schedule
Formula","Off")

and copy down and across, first replacing "Schedule Formula" with a
formula
that returns the schedule information for that worker, like

VLOOKUP($A2,WorkTable,2,False)

where WorkTable is a two column data table of names and schedules, like
so:

VLOOKUP($A2,$I$2:$J$50,2,False)

So the final formula would be something like:

=IF(ISERROR(MATCH($A2,OFFSET('[Year
Sched.xls]Sheet1'!$A$1,0,MATCH(B$1,'[Year
Sched.xls]Sheet1'!$A$1:$G$1,FALSE)-1,6,1),FALSE)),VLOOKUP($A2,$I$2:$J$50,2,False),"Of f")

HTH,
Bernie
MS Excel MVP


"ganga" wrote in message
...
Hi,

I have 2 work books. One is weekly schedule and other one is yearly
vacation
schedule. I would like to link both books matching names and dates.
Vacation
schedule is in monthly format and weekly schedule is in weekly format.

Eg: yearly vacation schdule
jan 1 Jan 2 Jan 3 and so on
ram siva
ganga

weekly schedule
jan 1 Jan 2 Jan 3
ram off 10-6 9-5
ganga off 9-5 11-7
siva 10-6 off 9-5

I need a formula in weekly schedule saying that if the date and name
match
in yearly schdule says "off" otherwise shift time.

Thank you in advance


.