Formula to Enter Dates
 

I have one worksheet that lists employee numbers in one column and dates
they have worked in the second column:
Emp # Date Worked
1233 12/1/06
1233 12/2/06
1233 12/3/06
1234 12/1/06
1234 12/2/06
1234 12/3/06

The other worksheet lists employee numbers in one column, then calendar
dates are listed along the top row:
12/1/06 12/2/06 12/3/06
1233
1244

What I would like to do, is pull the information from the worksheet with the
dates worked and have it put a 1 in the cell if that paricular employee
number worked that day, and a 0 in the cell for days not worked:

12/1/06 12/2/06 12/3/06 12/4/06 12/5/06
1233 1 1 1 0 0
1244 1 1 1 0 0


Can anyone help me with a formula that will do this?

Any help is appreciated.
Thanks

Formula to Enter Dates
 

Assuming the dates in row 1 and the employee numbers in column A, one
way:

B2: =SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),--(Sheet1!$B$2:$B$100=B$1))

Copy down and across as necessary.


In article ,
Jacq wrote:

I have one worksheet that lists employee numbers in one column and dates
they have worked in the second column:
Emp # Date Worked
1233 12/1/06
1233 12/2/06
1233 12/3/06
1234 12/1/06
1234 12/2/06
1234 12/3/06

The other worksheet lists employee numbers in one column, then calendar
dates are listed along the top row:
12/1/06 12/2/06 12/3/06
1233
1244

What I would like to do, is pull the information from the worksheet with the
dates worked and have it put a 1 in the cell if that paricular employee
number worked that day, and a 0 in the cell for days not worked:

12/1/06 12/2/06 12/3/06 12/4/06 12/5/06
1233 1 1 1 0 0
1244 1 1 1 0 0


Can anyone help me with a formula that will do this?

Any help is appreciated.
Thanks

Formula to Enter Dates
 

Try this:

Sheet1 A2:A7 =
1233 12/1/06
1233 12/2/06
1233 12/3/06
1234 12/1/06
1234 12/2/06
1234 12/3/06

Sheet2 B1:F1 =
12/1/06 12/2/06 12/3/06 12/4/06 12/5/06

Enter this formula on Sheet2 B2:

=SUMPRODUCT(--(Sheet1!$A$2:$A$7=$A2),--(Sheet1!$B$2:$B$7=B$1))

Copy across then down

Biff

"Jacq" wrote in message
...
I have one worksheet that lists employee numbers in one column and dates
they have worked in the second column:
Emp # Date Worked
1233 12/1/06
1233 12/2/06
1233 12/3/06
1234 12/1/06
1234 12/2/06
1234 12/3/06

The other worksheet lists employee numbers in one column, then calendar
dates are listed along the top row:
12/1/06 12/2/06 12/3/06
1233
1244

What I would like to do, is pull the information from the worksheet with
the
dates worked and have it put a 1 in the cell if that paricular employee
number worked that day, and a 0 in the cell for days not worked:

12/1/06 12/2/06 12/3/06 12/4/06 12/5/06
1233 1 1 1 0 0
1244 1 1 1 0 0


Can anyone help me with a formula that will do this?

Any help is appreciated.
Thanks

Formula to Enter Dates
 

Thanks so much for your help. It worked!

"JE McGimpsey" wrote:

Assuming the dates in row 1 and the employee numbers in column A, one
way:

B2: =SUMPRODUCT(--(Sheet1!$A$2:$A$100=$A2),--(Sheet1!$B$2:$B$100=B$1))

Copy down and across as necessary.


In article ,
Jacq wrote:

I have one worksheet that lists employee numbers in one column and dates
they have worked in the second column:
Emp # Date Worked
1233 12/1/06
1233 12/2/06
1233 12/3/06
1234 12/1/06
1234 12/2/06
1234 12/3/06

The other worksheet lists employee numbers in one column, then calendar
dates are listed along the top row:
12/1/06 12/2/06 12/3/06
1233
1244

What I would like to do, is pull the information from the worksheet with the
dates worked and have it put a 1 in the cell if that paricular employee
number worked that day, and a 0 in the cell for days not worked:

12/1/06 12/2/06 12/3/06 12/4/06 12/5/06
1233 1 1 1 0 0
1244 1 1 1 0 0


Can anyone help me with a formula that will do this?

Any help is appreciated.
Thanks

Formula to Enter Dates
 

Thanks so much for your help. It worked!

"T. Valko" wrote:

Try this:

Sheet1 A2:A7 =
1233 12/1/06
1233 12/2/06
1233 12/3/06
1234 12/1/06
1234 12/2/06
1234 12/3/06

Sheet2 B1:F1 =
12/1/06 12/2/06 12/3/06 12/4/06 12/5/06

Enter this formula on Sheet2 B2:

=SUMPRODUCT(--(Sheet1!$A$2:$A$7=$A2),--(Sheet1!$B$2:$B$7=B$1))

Copy across then down

Biff

"Jacq" wrote in message
...
I have one worksheet that lists employee numbers in one column and dates
they have worked in the second column:
Emp # Date Worked
1233 12/1/06
1233 12/2/06
1233 12/3/06
1234 12/1/06
1234 12/2/06
1234 12/3/06

The other worksheet lists employee numbers in one column, then calendar
dates are listed along the top row:
12/1/06 12/2/06 12/3/06
1233
1244

What I would like to do, is pull the information from the worksheet with
the
dates worked and have it put a 1 in the cell if that paricular employee
number worked that day, and a 0 in the cell for days not worked:

12/1/06 12/2/06 12/3/06 12/4/06 12/5/06
1233 1 1 1 0 0
1244 1 1 1 0 0


Can anyone help me with a formula that will do this?

Any help is appreciated.
Thanks

Formula to Enter Dates
 

You're welcome. Thanks for the feedback!

Biff

"Jacq" wrote in message
...
Thanks so much for your help. It worked!

"T. Valko" wrote:

Try this:

Sheet1 A2:A7 =
1233 12/1/06
1233 12/2/06
1233 12/3/06
1234 12/1/06
1234 12/2/06
1234 12/3/06

Sheet2 B1:F1 =
12/1/06 12/2/06 12/3/06 12/4/06 12/5/06

Enter this formula on Sheet2 B2:

=SUMPRODUCT(--(Sheet1!$A$2:$A$7=$A2),--(Sheet1!$B$2:$B$7=B$1))

Copy across then down

Biff

"Jacq" wrote in message
...
I have one worksheet that lists employee numbers in one column and
dates
they have worked in the second column:
Emp # Date Worked
1233 12/1/06
1233 12/2/06
1233 12/3/06
1234 12/1/06
1234 12/2/06
1234 12/3/06

The other worksheet lists employee numbers in one column, then calendar
dates are listed along the top row:
12/1/06 12/2/06 12/3/06
1233
1244

What I would like to do, is pull the information from the worksheet
with
the
dates worked and have it put a 1 in the cell if that paricular employee
number worked that day, and a 0 in the cell for days not worked:

12/1/06 12/2/06 12/3/06 12/4/06 12/5/06
1233 1 1 1 0 0
1244 1 1 1 0 0


Can anyone help me with a formula that will do this?

Any help is appreciated.
Thanks