Help with a complicated VLOOKUP an IF Formula (WIN7, Excel 2007)

[ozzborn]

	C	D	E	F	G
6	Date	Name	Star	End	Days
7	data entry date	name of employee	Leave start	leave End	Days on Leave
8	1 Feb 15	Smith	4 Apr 15	8 Apr 15	4​
9	15 Feb	Wilson	12 May 15	17 May 15	6

<tbody>
</tbody>

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.

	J	K	L
6	Name	Leave Taken	Leave Planned
7	Smith	12	6
8	employee2
9	employee3
10	employee4
11	employee5
12	employee6
13	employee7

<tbody>
</tbody>

 

[steve the fish]

You shouldn't need to change anything until you have data extending past row 100.

 

[ozzborn]

Yes, I will have to extend that out to 300 ....the sheet I am building tracks 10 employees. Yes I copied the foumulas down for the employees and it works no need to change the absolute referances. How can I test it ....can I replace TODAY() with a date for testing?

 

[steve the fish]

Easiest way is to put your test date in a cell and replace the today() with the cell reference. If you wish to copy it down don't forget the $ signs (press F4) if you want to keep that date absoluted.

 

[ozzborn]

Ok, that works perfect. THANK YOU for your help. I been studing this formula so I understand how this works. Thank you again.