Create summary sheet

trebor1956

Board Regular
Joined
Jul 2, 2015
Messages
67
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
From DateTo DateHalf day?Hols Left
14/12/201715/12/201728
21/12/201721/12/2017Y0.57.5
02/01/201802/01/201816.5

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>
Worksheet for Andy
From DateTo DateHalf day?Hols Left
03/01/201805/01/201837
02/02/201803/02/2018Y0.56.5
03/03/201808/03/201842.5

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>
<strike></strike>Summary sheet

RobAndyDeneRobAndyDeneRobAndyDene
01/01/201801/02/201801/03/2018
02/01/201802/02/201802/03/2018
03/01/201803/02/201803/03/2018
04/01/201804/02/201804/03/2018
05/01/201805/02/201805/03/2018
06/01/201806/02/201806/03/2018
07/01/201807/02/201807/03/2018
08/01/201808/02/201808/03/2018
09/01/201809/02/201809/03/2018
10/01/201810/02/201810/03/2018
11/01/201811/02/201811/03/2018
12/01/201812/02/201812/03/2018
13/01/201813/02/201813/03/2018
14/01/201814/02/201814/03/2018
15/01/201815/02/201815/03/2018
16/01/201816/02/201816/03/2018
17/01/201817/02/201817/03/2018
18/01/201818/02/201818/03/2018
19/01/201819/02/201819/03/2018
20/01/201820/02/201820/03/2018
21/01/201821/02/201821/03/2018
22/01/201822/02/201822/03/2018
23/01/201823/02/201823/03/2018
24/01/201824/02/201824/03/2018
25/01/201825/02/201825/03/2018
26/01/201826/02/201826/03/2018
27/01/201827/02/201827/03/2018
28/01/201828/02/201828/03/2018
29/01/201829/03/2018
30/01/201830/03/2018
31/01/201831/03/2018

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

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



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

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

crazydragon84

Board Regular
Joined
Feb 19, 2015
Messages
195
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

New Member
Joined
Feb 18, 2014
Messages
39
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!
 

Forum statistics

Threads
1,089,267
Messages
5,407,275
Members
403,131
Latest member
Lewas2019

This Week's Hot Topics

Top