[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Date
[/TD]
[TD="align: center"]Name
[/TD]
[TD="align: center"]Star
[/TD]
[TD="align: center"]End
[/TD]
[TD="align: center"]Days
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD="align: center"]data entry date
[/TD]
[TD="align: center"]name of employee
[/TD]
[TD="align: center"]Leave start
[/TD]
[TD="align: center"]leave End
[/TD]
[TD="align: center"]Days on Leave
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD="align: center"]1 Feb 15
[/TD]
[TD="align: center"] Smith
[/TD]
[TD="align: center"]4 Apr 15
[/TD]
[TD="align: center"]8 Apr 15
[/TD]
[TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD="align: center"]15 Feb
[/TD]
[TD="align: center"]Wilson
[/TD]
[TD="align: center"]12 May 15
[/TD]
[TD="align: center"]17 May 15
[/TD]
[TD="align: center"]6
[/TD]
[/TR]
</tbody>[/TABLE]
The Table above is a flat file database. I have a VBA userform built to allow employees to enter their data as employees plan to take leave. The table is dynamic and builds as employees enter there planned leave dates.
I need to build another spread sheet that calculates leave that has been taken and leave that is planned to be taken during the FY.The problem I am having is that there will be multi incidences of each employee name in Column J. I believe that the problem can be solved with a VLOOKUP formula and an IF statement but I am not sure how to begin. Example in K7 I wouldl like that cell to calculate all leave days prior to the TODAY() functionand return all leave days taken during the year and in L6 I would like that cell to calculate leave planned before the TODAY() function. I thank you in advance for any assistance.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Name
[/TD]
[TD="align: center"]Leave Taken
[/TD]
[TD="align: center"]Leave Planned
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]Smith
[/TD]
[TD="align: center"]12
[/TD]
[TD="align: center"]6
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]employee2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]employee3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]employee4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: center"]employee5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: center"]employee6
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD="align: center"]employee7
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Date
[/TD]
[TD="align: center"]Name
[/TD]
[TD="align: center"]Star
[/TD]
[TD="align: center"]End
[/TD]
[TD="align: center"]Days
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD="align: center"]data entry date
[/TD]
[TD="align: center"]name of employee
[/TD]
[TD="align: center"]Leave start
[/TD]
[TD="align: center"]leave End
[/TD]
[TD="align: center"]Days on Leave
[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD="align: center"]1 Feb 15
[/TD]
[TD="align: center"] Smith
[/TD]
[TD="align: center"]4 Apr 15
[/TD]
[TD="align: center"]8 Apr 15
[/TD]
[TD]
4
[/TD][/TR]
[TR]
[TD]9
[/TD]
[TD="align: center"]15 Feb
[/TD]
[TD="align: center"]Wilson
[/TD]
[TD="align: center"]12 May 15
[/TD]
[TD="align: center"]17 May 15
[/TD]
[TD="align: center"]6
[/TD]
[/TR]
</tbody>[/TABLE]
The Table above is a flat file database. I have a VBA userform built to allow employees to enter their data as employees plan to take leave. The table is dynamic and builds as employees enter there planned leave dates.
I need to build another spread sheet that calculates leave that has been taken and leave that is planned to be taken during the FY.The problem I am having is that there will be multi incidences of each employee name in Column J. I believe that the problem can be solved with a VLOOKUP formula and an IF statement but I am not sure how to begin. Example in K7 I wouldl like that cell to calculate all leave days prior to the TODAY() functionand return all leave days taken during the year and in L6 I would like that cell to calculate leave planned before the TODAY() function. I thank you in advance for any assistance.
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD="align: center"]Name
[/TD]
[TD="align: center"]Leave Taken
[/TD]
[TD="align: center"]Leave Planned
[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD="align: center"]Smith
[/TD]
[TD="align: center"]12
[/TD]
[TD="align: center"]6
[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD="align: center"]employee2
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD="align: center"]employee3
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD="align: center"]employee4
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD="align: center"]employee5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD="align: center"]employee6
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD="align: center"]employee7
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]