match

G

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
 
B

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
 
G

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

Bernie Deitrick said:
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 said:
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


.
 
B

Bernie Deitrick

Make the obvious changes to my email address

deitbe at consumer dot org.

Bernie


ganga said:
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 said:
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 said:
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


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Please help me -urgent 1
please help me-urgent 5
look up 0
Adding rota 0
vacation 1
Yearly sum 3
Date Intervals 1
lookup 0

Top