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

ozzborn

Board Regular
Joined
Sep 14, 2011
Messages
84
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
[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]
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]
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
 
Upvote 0
What is leave? How do you plan to cope with part timers? Do people work every day?
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
the first formula you are recommending be placed in L7? and the 2nd in K7?
 
Upvote 0
Yes first one in L7.

Just change the greater than sign to less than equal to and put it in K7.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,492
Messages
6,172,599
Members
452,466
Latest member
Lynlindsay

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top