Thanks:  0
Likes:  0

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

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

 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

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

2. ## Re: Help with a complicated VLOOKUP an IF Formula (WIN7, Excel 2007)

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

3. ## Re: Help with a complicated VLOOKUP an IF Formula (WIN7, Excel 2007)

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

4. ## Re: Help with a complicated VLOOKUP an IF Formula (WIN7, Excel 2007)

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.

5. ## Re: Help with a complicated VLOOKUP an IF Formula (WIN7, Excel 2007)

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.

6. ## Re: Help with a complicated VLOOKUP an IF Formula (WIN7, Excel 2007)

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

But try these:

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

or in other column

=SUMPRODUCT(--(\$D\$8:\$D\$100=K7),--(\$F\$8:\$F\$100
It keeps cutting up my formula for some reason??

7. ## Re: Help with a complicated VLOOKUP an IF Formula (WIN7, Excel 2007)

That K should be a J

8. ## Re: Help with a complicated VLOOKUP an IF Formula (WIN7, Excel 2007)

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

9. ## Re: Help with a complicated VLOOKUP an IF Formula (WIN7, Excel 2007)

Yes first one in L7.

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

10. ## Re: Help with a complicated VLOOKUP an IF Formula (WIN7, Excel 2007)

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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•