Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

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

  1. #1
    Board Regular
    Join Date
    Sep 2011
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    New Member
    Join Date
    Aug 2014
    Location
    Perth, Australia
    Posts
    23
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,052
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default 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. #4
    Board Regular
    Join Date
    Sep 2011
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by ozzborn; Sep 15th, 2014 at 10:16 AM.

  5. #5
    Board Regular
    Join Date
    Sep 2011
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by ozzborn; Sep 15th, 2014 at 10:17 AM.

  6. #6
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,052
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default 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??
    Last edited by steve the fish; Sep 15th, 2014 at 10:43 AM.

  7. #7
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,052
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

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

    That K should be a J

  8. #8
    Board Regular
    Join Date
    Sep 2011
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,052
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    2 Thread(s)

    Default 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. #10
    Board Regular
    Join Date
    Sep 2011
    Posts
    64
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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