Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,568
    Thanks
    141
    Thanked 12 Times in 12 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,568
    Thanks
    141
    Thanked 12 Times in 12 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,568
    Thanks
    141
    Thanked 12 Times in 12 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
  •