Search and paste data from one workbook to another / 2 criteria

[alex_i]

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:

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!

 

[alex_i]

Bump?

 

[bhos123]

is your 3-Jul, 4-Jul ...they are text values or Dates, can you populate them as same format as Col B, 03/07/2016, 04/07/2016 and so on...

 

[alex_i]

Shouldn't be a problem

 

[bhos123]

1> In Sheet1, Insert a column at C, then put C2= A2&"-"&B2 drag down
2> In Sheet2 , assuming you have Clock Number in Col C and 3-Jul in Col D, so put D3=Vlookup($C3&"-"&D$1,Sheet1!C:D,2,0) and drag to right and Down.

 

[alex_i]

Thank you so much! Will be checking if it is working properly on Monday and update the thread. Cheers

 

[alex_i]

Thank you so much! Will be checking if it is working properly on Monday and update the thread. Cheers

Now it is all working. Thanks again! What I had to figure out apart from adjusting the cell coordinates to my actual workbook was to figure out why it is working for the first day (3 - Jul) only. Then I found out that before I drag the formula in sheet2 sideways I had to add $s before C and D as follows:
=VLOOKUP($D8&"-"&F$6,Sheet1!$C:$D,2,0)

Also if anyone is using that solution - expect to meet #N/A in the cells where no data is available, which will have an impact on formulas having that particular cell in their range. In my case it was SUM(range). I had to change it to =SUMIF(RANGE, "<>#N/A") which worked as a charm.

Thanks a lot for the help!

 

[bhos123]

you are welcome!