Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Formula error Sumproduct , combined wth Index and match

    I am trying to determine which employee/employees are on leave in the current month, but get an incorrect solution-see F6
    Attached Files Attached Files

  2. #2
    Lounger
    Join Date
    Feb 2011
    Posts
    28
    Thanks
    0
    Thanked 10 Times in 8 Posts
    I tried a slightly different approach and can get it to work with numbers but can't get it to work with letters; instead of an "a" character to indicate leave I have had to use a "1" (but it could be any number). Not sure if this will help you, but hope that it does!
    Attached Files Attached Files

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the help, much appreciated

    Please explain the following

    1) having two commas ,, after .... November, December),,match
    2) why + 2 after MATCH(A6,'Leave Register'!$C$2:$D$2)+2 ?
    3) significance of - 3 in this section of formula MONTH($F$1)-3))>

    Code:
     =IF(ISERROR(MATCH(A6,'Leave Register'!$C$2:$D$2)),"Employee not in Leave Register",IF(MAX(INDEX((April,May,June,July,August,September,October,November,December),,MATCH(A6,'Leave Register'!$C$2:$D$2)+2,MONTH($F$1)-3))>0,"Yes","No"))

  4. #4
    Lounger
    Join Date
    Feb 2011
    Posts
    28
    Thanks
    0
    Thanked 10 Times in 8 Posts
    The two commas are because that's where you would put row number if you were looking for a specific row, but you just want to know if there's something anywhere in the column so it's left blank.
    The +2 is because the names start in column 3, not column 1 on the second sheet.
    The -3 is because April is the first month in the list, but is month 4.

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Thanks for the info. I now fully understand the logic

Posting Permissions

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