Create summary sheet

[trebor1956]

Hi I am using excel 2013. I have individual sheets for each team member that use 'networkdays' to calculate holiday used between a start and end date. I would like to put a letter 'R', 'A' or 'D' in columns in a summary sheet depending who has booked holiday.
I can't attach the workbook I am trying on so I have copied the relevant sheets below. I just can't seem to get my head round it so have cleared all my attempts so far. Any help would be greatly received.

Worksheet for Rob
[TABLE="width: 345"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 345"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 345"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]From Date[/TD]
[TD]To Date[/TD]
[TD]Half day?[/TD]
[TD][/TD]
[TD]Hols Left[/TD]
[/TR]
[TR]
[TD="align: right"]14/12/2017[/TD]
[TD="align: right"]15/12/2017[/TD]
[TD][/TD]
[TD]2[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]21/12/2017[/TD]
[TD="align: right"]21/12/2017[/TD]
[TD]Y[/TD]
[TD]0.5[/TD]
[TD="align: right"]7.5[/TD]
[/TR]
[TR]
[TD="align: right"]02/01/2018[/TD]
[TD="align: right"]02/01/2018[/TD]
[TD][/TD]
[TD]1[/TD]
[TD="align: right"]6.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Worksheet for Andy
[TABLE="width: 345"]
<colgroup><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD]From Date[/TD]
[TD]To Date[/TD]
[TD]Half day?[/TD]
[TD][/TD]
[TD]Hols Left[/TD]
[/TR]
[TR]
[TD="align: right"]03/01/2018[/TD]
[TD="align: right"]05/01/2018[/TD]
[TD][/TD]
[TD]3[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]02/02/2018[/TD]
[TD="align: right"]03/02/2018[/TD]
[TD]Y[/TD]
[TD]0.5[/TD]
[TD="align: right"]6.5[/TD]
[/TR]
[TR]
[TD="align: right"]03/03/2018[/TD]
[TD="align: right"]08/03/2018[/TD]
[TD][/TD]
[TD]4[/TD]
[TD="align: right"]2.5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>Summary sheet

[TABLE="width: 603"]
<colgroup><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Rob[/TD]
[TD]Andy[/TD]
[TD]Dene[/TD]
[TD][/TD]
[TD]Rob[/TD]
[TD]Andy[/TD]
[TD]Dene[/TD]
[TD][/TD]
[TD]Rob[/TD]
[TD]Andy[/TD]
[TD]Dene[/TD]
[/TR]
[TR]
[TD]01/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]02/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]03/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]03/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]04/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]04/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]05/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]06/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]06/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]06/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]07/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]08/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]09/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]09/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]12/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]14/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]15/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]17/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]18/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]22/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]23/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]24/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]24/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]25/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]26/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]27/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]27/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28/02/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]28/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]31/01/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31/03/2018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<strike></strike>
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[crazydragon84]

dude i have no idea what you are trying to accomplish or what the actual summary sheet should look like... you need to clarify more.

 

[TimNevins]

Not to be a pest, but do these people accrue holiday hours throughout the year? In order to calculate remaining holiday you need to account for those hours. I think what you need to do is make an entry for each day in the user sheets, then you can use index, match to grab the values by day from each user sheet. As it is, you are going to have to parse those multiday values to be able to get discreet values for each date.

Good luck!