Hi everyone,
I've been pulling my hairs in the last 3 weeks trying to find a solution to my Excel (2013) problem online - unfortunately without any success, so I've finally decided to post it and see if anyone would be willing to help.
Anyway, here is the deal:
I have two spreadsheets:
1. Sheet1 is a report that has 3 columns:
A – Employee N
B – Date (the report is weekly so there is a maximum of 7 different values)
C – Hours worked
Example:
<tbody>
</tbody>
2. Sheet2 has a little bit more info and it looks like that:
<tbody>
</tbody>
At the moment I am reading the report (sheet1) and manually typing in the hours each worker did in sheet2 on a weekly basis.
If cell FORNAMES is A1, can I make cell E3 to search the whole report (sheet1) and display the value it finds based on two criteria (date and employee/clock N must both match)? Obviously I will extend the formula amongst all cells in the range E3:K6
If there is no such record in the report (sheet1) just leave the relevant cell in sheet2 empty.
Any suggestions?
If more details are required I will be monitoring the thread closely and try to reply asap.
Thanks for your help!
I've been pulling my hairs in the last 3 weeks trying to find a solution to my Excel (2013) problem online - unfortunately without any success, so I've finally decided to post it and see if anyone would be willing to help.
Anyway, here is the deal:
I have two spreadsheets:
1. Sheet1 is a report that has 3 columns:
A – Employee N
B – Date (the report is weekly so there is a maximum of 7 different values)
C – Hours worked
Example:
Employee N | Date | Hours worked |
80015 | 07/07/2016 | 7.75 |
80015 | 08/07/2016 | 7 |
80016 | 05/07/2016 | 8 |
80016 | 06/07/2016 | 8 |
80016 | 08/07/2016 | 8 |
80017 | 03/07/2016 | 8.75 |
80017 | 05/07/2016 | 8 |
80017 | 06/07/2016 | 8.75 |
80017 | 08/07/2016 | 8.75 |
80018 | 03/07/2016 | 8 |
80018 | 04/07/2016 | 8 |
80018 | 05/07/2016 | 8 |
80018 | 06/07/2016 | 8 |
80018 | 08/07/2016 | 8 |
<tbody>
</tbody>
2. Sheet2 has a little bit more info and it looks like that:
FORNAMES | SURNAME | CLOCK | 3-Jul | 4-Jul | 5-Jul | 6-Jul | 7-Jul | 8-Jul | 9-Jul | TOTAL | |
NUMBER | SUN | MON | TUE | WED | THU | FRI | SAT | HOURS | |||
80015 | 7.75 | 7 | 0.00 | ||||||||
80016 | 8 | 8 | 8 | 0.00 | |||||||
80017 | 8.75 | 8 | 8.75 | 8.75 | 0.00 | ||||||
80018 | 8 | 8 | 8 | 8 | 8 | 0.00 |
<tbody>
</tbody>
At the moment I am reading the report (sheet1) and manually typing in the hours each worker did in sheet2 on a weekly basis.
If cell FORNAMES is A1, can I make cell E3 to search the whole report (sheet1) and display the value it finds based on two criteria (date and employee/clock N must both match)? Obviously I will extend the formula amongst all cells in the range E3:K6
If there is no such record in the report (sheet1) just leave the relevant cell in sheet2 empty.
Any suggestions?
If more details are required I will be monitoring the thread closely and try to reply asap.
Thanks for your help!