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

#### ozzborn

##### Board Regular
 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>

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hey there,

Probably not as clean as what you're looking for but about a SUMIF that checks on a column you would have hidden on the side which counts the number of days providing the end-date for leave is greater than TODAY()?

IM

What is leave? How do you plan to cope with part timers? Do people work every day?

That is an interesting and simple idea. I assume you mean next to the days column G6 in the dynamic spreadsheet? However, it does not solve the problem of multiple instences of leave for the same employee. It could be two column one for leave taken and one for leave planned. Just dup the fomula down so when the dynamic portion of it is populated it auto calculates.

I am thinking I "=IF(\$K7>\$C\$3,L7-K7,0)" for leave planned and "=IF(\$K7<\$C\$3,L7-K7,0)" for leave taken. Where \$C\$3 is todays date "TODAY()"But I need the VLOOKUP to deal with the multi instences of employees.

Last edited:
Steve each employee gets 30 days leave annually. I am just tracking when they take it and weekends do not matter. Leaver from Fri to Mon is still 4 days leave.

Last edited:
Not sure id like to work with you counting the weekend as leave!

But try these:
<today()),\$g\$8:\$g\$100)

=SUMPRODUCT(--(\$D\$8:\$D\$100=K7),--(\$F\$8:\$F\$100>TODAY()),\$G\$8:\$G\$100)</today()),\$g\$8:\$g\$100)

or in other column

=SUMPRODUCT(--(\$D\$8:\$D\$100=K7),--(\$F\$8:\$F\$100<TODAY()),\$G\$8:\$G\$100)

It keeps cutting up my formula for some reason??

Last edited:
That K should be a J

the first formula you are recommending be placed in L7? and the 2nd in K7?

Yes first one in L7.

Just change the greater than sign to less than equal to and put it in K7.

Yes, I did that... and it seems to be work. I have never used SUMPRODUCT before that seems to be working. each additional name I have to change the absolute cell referances in the fomula but it seems to work.

Replies
0
Views
247
Replies
4
Views
163
Replies
9
Views
410
Replies
1
Views
90
Replies
5
Views
376

1,207,439
Messages
6,078,569
Members
446,349
Latest member
Malroos7912

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back