hours from job-sheet to time-sheet

[Gary]

We have a sheet titled Job-Sheet. In column A1-A20 there's days of a week.
In column B1-B20 there's names. In column C1-C20 there are hours.



MON Anderson, Josh 8

MON Bailey, John 8

MON Blakely, Brett 8

TUE Eddings, Cindy 8

TUE Floyd, Bill 8

TUE Anderson, Josh 8

WED Bailey, John 9

WED Blakely, Brett 9

WED Eddings, Cindy 9

THUR Anderson, Josh 10

THUR Bailey, John 10

FRI Anderson, Josh 10

SAT Anderson, Josh 9

SAT Bailey, John 9

SAT Blakely, Brett 9

SAT Eddings, Cindy 9



We have a second sheet titled Time-Sheet. Each employee has a timesheet
made of rows in column E through K making up a week.



MON TUE
WED THU FRI SAT

Anderson, Josh 8 8
0 10 10 10



If an employee works on Monday we want those hours automatically listed
under MON in the timesheet cells. How would we show hours in the time-sheet
based on the job-sheet?



I have written a similar question prior to this, but hopefully this one is
clearer. We presently wish to do this without the use of a Pivot Table.



Thank you,

Gary

 

[Access101]

I have a workbook with Sheet 1, and Sheet 2.

I put my cursor in Sheet 1, A1 and enter the equals sign.

I now use the mouse to select Sheet 2, and randomly select cell A6 and press
enter.

The formula stored in Sheet 1 cell A1 now reads: =Sheet2!A6

If you update Sheet 2 cell A6, it will be reflected in Sheet 1 A1

 

[T. Valko]

I posted a link to a sample file that demonstrates this in your other post.

Biff

 

[Gary]

Biff,

I did receive and applied your formula in an earlier post, and it worked
perfect. That case referred to a list of employees, miles and totaling the
miles to the proper employee.
I attempted to use the same concept on this application without success. It
seemed similar but I got of track when limiting one employees hours to one
specific day. I see below that my time-sheet example did not translate well
in the email, but if a formula worked it would return a result for
"Anderson, Josh" as 8 hours in the Monday cell, 8 hours in the Tuesday cell,
0 hours in the Wednesday cell, 10 hours in the Thursday cell, 10 hours in
the Friday cell and 9 hours in the Saturday cell. Each employee on the crew
would have his/her own time-sheet with hours worked on a specific day being
displayed in a cell under that day.

Thank you for any further consideration of my question.

Regards,
Gary

 

[T. Valko]

Ok, now I'm confused!

You said the sample worked which was based on hours worked (your posted
sample) but you say the actual data was for total miles and now you want the
same thing for hours worked but it doesn't work?

Biff

 

[Gary]

Bif,

I'm sorry for the confusion. In short the two cases are all together
different. The answer to the first question you responded to does not fit
this second and different question. I appreciate your efforts but to avoid
additional confusion, will look otherwise for an answer. Thank you much for
the time.

Regards,
Gary

 

[T. Valko]

Ok, good luck!

Biff

Bif,

I'm sorry for the confusion. In short the two cases are all together
different. The answer to the first question you responded to does not fit
this second and different question. I appreciate your efforts but to
avoid additional confusion, will look otherwise for an answer. Thank you
much for the time.

Regards,
Gary

 

[shriil]

We have a sheet titled Job-Sheet. In column A1-A20 there's days of a week.
In column B1-B20 there's names. In column C1-C20 there are hours.

MON Anderson, Josh 8

MON Bailey, John 8

MON Blakely, Brett 8

TUE Eddings, Cindy 8

TUE Floyd, Bill 8

TUE Anderson, Josh 8

WED Bailey, John 9

WED Blakely, Brett 9

WED Eddings, Cindy 9

THUR Anderson, Josh 10

THUR Bailey, John 10

FRI Anderson, Josh 10

SAT Anderson, Josh 9

SAT Bailey, John 9

SAT Blakely, Brett 9

SAT Eddings, Cindy 9

We have a second sheet titled Time-Sheet. Each employee has a timesheet
made of rows in column E through K making up a week.

MON TUE
WED THU FRI SAT

Anderson, Josh 8 8
0 10 10 10

If an employee works on Monday we want those hours automatically listed
under MON in the timesheet cells. How would we show hours in the time-sheet
based on the job-sheet?

I have written a similar question prior to this, but hopefully this one is
clearer. We presently wish to do this without the use of a Pivot Table.

Thank you,

Gary

Hi

I was thinking if this could be done the under-mentioned way:
Assumptions:

Range specified in the "Job Sheet" does not expand but is limited
within A1 - C20

For each Day there can be a maximum of 5 rows and a minimum 1 row

Then, in the second sheet, i.e. the Time-Sheet where u have the table
as follows

Mon Tue Wed

 

[shriil]

Hi Gary

I was thinking if this could be done the following way, of course with
some assumptions:

The Assumptions are

For each day of the week, there can be maximum 4 rows, and minimum 1
row.

The data is input as per progressive days of the week, i.e. Mondays
come on top, then Tuesdays, then Wed.... (Or other wise u can add
another column, Give an ID no. corresponding to the day, and then sort
the Range)


Then, in the 2nd sheet, i.e. the Time sheet, where the table formation
is like
Col D Col E Col F Col G Col
H .........
Row 29 Mon Tue Wed Thu
Fri Sat

Row 30: Anderson, Josh XX YY

Row 31Bailey, John ZZ

Row 32 Blakely, Brett

Row 33 Eddings, Cindy

... . and so on....

Then for Cell XX the formula can be:

=IF(AND(E29=$A1,$D30=$B1),$C1,IF(AND(E29=$A2,$D30=$B2),$C2,IF(AND(E29=
$A3,$D30=$B3),$C3,IF(AND(E29=$A4,$D30=$B4),$C4,0))))

For Cell YY and subsequent cells on that row, copy the formula of Cell
XX and paste.

For Cell ZZ , the fomula will be similar to the formula of the
preceding row, albeit with minor changes

=IF(AND(E29=$A2,$D31=$B2),$C2,............. ,IF(AND(E29=$A8,$D31=$B8),
$C8,0))))))

... well a bit cumbersome I guess...

If this can help u in opening another channel of thought.. that would
be great.

Sanjib