Turning a series of individual timesheets into a simple daily summary

[Acesario]

So in my spreadsheet I have a tab for each employee. Each tab is a weekly summary with a column for the date/hours/mileage/job that they worked. I want to create a separate daily spreadsheet that gives me a list of each employee that worked on a given day, and also pull the # of hours they worked, the mileage, and the job. I would ideally like this list to be concise (i.e. not include people who did not work that day, and also not have any blanks).

I think if I could get the list of employee names figured out I could easily do the rest, but I'm not sure where to start? Is this even something I can do with excel?

 

[synergy16]

this could very easily done with a simple vlookup formula.

 

[oldbrewer]

bill

fred

date

hours

mileage

job

date

hours

mileage

job

01/07/2016

8

123

job1

bill

formula is

=IF(B3>0,$A$1,"")

01/07/2016

6

75

job6

fred

02/07/2016

8

133

job2

bill

02/07/2016

8

98

job7

fred

03/07/2016

10

167

job3

bill

03/07/2016

12

135

job8

fred

04/07/2016

8

95

job4

bill

04/07/2016

0

05/07/2016

8

110

job5

bill

05/07/2016

8

65

job10

fred

I would first of all pull all data onto a new master sheet as above

(assumes you only have 2 employees

if data on each tab is as above ie A1:D7

then K1 (shows fred) is =sheet2!A1

drag across 3 and down 6 to get the info

could do this with a macro easily if you have 50 employees

01/07/2016

bill 8 hours 123 miles on job1 fred 6 hours 75 miles on job6

02/07/2016

bill 8 hours 133 miles on job2 fred 8 hours 98 miles on job7

03/07/2016

bill 10 hours 167 miles on job3 fred 12 hours 135 miles on job8

04/07/2016

bill 8 hours 95 miles on job4

05/07/2016

bill 8 hours 110 miles on job5 fred 8 hours 65 miles on job10

formula giving first row of summary table

=IF(E3="","",E3&" "&B3&" hours "&C3&" "&"miles on "&D3)&" "&IF(O3="","",O3&" "&L3&" hours "&M3&" "&"miles on "&N3)

THIS IS A CRAZY WAY TO HANDLE DATA SO SEE NEXT POST IN 5 MINUTES

<colgroup><col><col span="5"><col><col span="3"><col><col span="6"></colgroup><tbody>
</tbody>

 

[Acesario]

Employee Name	Mike P	Hours	Hours	Hours	Hours	Hours
Date <tbody> </tbody>	Job	Saturday	Sunday	Monday	Tuesday	Wednesday
8/6/16	Garbage	0
8/7/16			3
8/8/16				5

<tbody>
</tbody>

Here is a simplified version of my weekly summary spreadsheets, I have one of these for every employee, I'm trying v-lookup now and it doesn't seem like it will work because the person's name is not in every row next to the date, I suppose I could hide a row and do it that way but other than editing all 15 sheets is there another way?

I'm looking at doing an if statement that returns the value of the cell where the name is only if there is a positive value in the hours column for the given day, which seems to work but I'm not sure how to "simplify" it so that if the first person didn't work that day, it automatically looks at the next person, and so on? I was trying to use a combination with the "indirect" function and a named group which has all the sheet names in it but I can't quite get the formula down:
=IF(COUNTIF('[Weekly Summary.xlsx]SheetNames'!$A$6:$A$12,A1)>0,'[Weekly Summary.xlsx]SheetNames'!$K$3:$R$3)

When I try this it only reads the first value of the list "sheetnames" and therefore only looks at the first sheet.

 

[oldbrewer]

											hours worked table
	date	hours	mileage	job						01/07/2016	02/07/2016	03/07/2016	04/07/2016	05/07/2016
bill	01/07/2016	8	123	job1					fred	6	8	12	0	8
bill	02/07/2016	8	133	job2					bill	8	8	10	8	8
bill	03/07/2016	10	167	job3
bill	04/07/2016	8	95	job4
bill	05/07/2016	8	110	job5
fred	01/07/2016	6	75	job6
fred	02/07/2016	8	98	job7
fred	03/07/2016	12	135	job8
	04/07/2016	0									mileage table
fred	05/07/2016	8	65	job10						01/07/2016	02/07/2016	03/07/2016	04/07/2016	05/07/2016
									fred	75	98	135	0	65
									bill	123	133	167	95	110
											jobs table
										01/07/2016	02/07/2016	03/07/2016	04/07/2016	05/07/2016
									fred
									bill
										not sure how to do this but is this aproach possible

<colgroup><col><col><col span="5"><col><col span="2"><col><col><col><col><col><col></colgroup><tbody>
</tbody>