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