match

[ganga]

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

 

[Bernie Deitrick]

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),"Off")

HTH,
Bernie
MS Excel MVP

 

[ganga]

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

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),"Off")

HTH,
Bernie
MS Excel MVP

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

.

 

[Bernie Deitrick]

Make the obvious changes to my email address

deitbe at consumer dot org.

Bernie

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

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),"Off")

HTH,
Bernie
MS Excel MVP

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

.