Results 1 to 6 of 6

Thread: If+index
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2015
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question If+index

    HI ALL,


    I'm planning to use some formulas to return my employee's schedule which looks clean and more simply from a complicated database.
    Here are what i have in the original schedule @ sheet_schedule for my 3x employee and a formula to return a concise layout @ sheet_result




    formula in cell B51.....etc
    Code:
    =IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),7,5)),0)),"hh:mm"))
    my first question is how can i amend with my above formula to return "AH09 Day 1.0/Vacation Leave Day 1.0" for Peter instead of "00:00 - 00:00" in cell D53/F53/H53?

    thanks ALL for the great help

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If+index

    Quote Originally Posted by kelvin_9 View Post
    HI ALL,


    I'm planning to use some formulas to return my employee's schedule which looks clean and more simply from a complicated database.
    Here are what i have in the original schedule @ sheet_schedule for my 3x employee and a formula to return a concise layout @ sheet_result




    formula in cell B51.....etc
    Code:
    =IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),7,5)),0)),"hh:mm"))
    my first question is how can i amend with my above formula to return "AH09 Day 1.0/Vacation Leave Day 1.0" for Peter instead of "00:00 - 00:00" in cell D53/F53/H53?

    thanks ALL for the great help
    any help

  3. #3
    Board Regular
    Join Date
    Mar 2015
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If+index

    Quote Originally Posted by kelvin_9 View Post
    any help
    is it impossible to do it as my formula is too many requirement

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If+index

    You might try something like this

    Code:
    =IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",if TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")="00:00",INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)),TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),7,5)),0)),"hh:mm")))

  5. #5
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If+index

    Sorry, I was missing a parentheses in the above reply. Please use this instead

    Code:
    =IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",if(TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")="00:00",INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)),TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),7,5)),0)),"hh:mm")))

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: If+index

    Quote Originally Posted by brittymcsmartypants View Post
    Sorry, I was missing a parentheses in the above reply. Please use this instead

    Code:
    =IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",if(TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")="00:00",INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)),TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)):INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A52,SCHEDULE!$A:$A,0)-1),7,5)),0)),"hh:mm")))
    thanks man, it's really fantasic

    with character limitation of macro
    is it mean one more range need to assign to run the new formula?

    Code:
    Sub Macro003()'
    ' Macro003 Macro
    '
    
    
    '
        Sheets("result").Select
        Dim rDate As Range, rDest As Range
        Dim sText1 As String, sText2 As String
    
    
        Set rDate = Range("c50")
        Set rDest = Range("c51")
    
    
        sText1 = "TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX('schedule'!$C:$C,MATCH('result'!$A51,'schedule'!$A:$A,0)):INDEX('schedule'!$C:$C,MATCH('result'!$A52,'schedule'!$A:$A,0)-1),5)),1)),""hh:mm"")"
        sText2 = "TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX('schedule'!$C:$C,MATCH('result'!$A51,'schedule'!$A:$A,0)):INDEX('schedule'!$C:$C,MATCH('result'!$A52,'schedule'!$A:$A,0)-1),7,5)),0)),""hh:mm"")"
    
    
        rDest.FormulaArray = "=IF(INDEX('schedule'!$C:$C,MATCH('result'!$A51,'schedule'!$A:$A,0))="""","""",1111&"" - ""&2222)"
        rDest.Replace "1111", sText1, LookAt:=xlPart
        rDest.Replace "2222", sText2, LookAt:=xlPart
    
    
        Range("c51").AutoFill Destination:=Range("c51:c" & Range("a" & Rows.Count).End(xlUp).Row)
    
    
        Range("b51").Select
        ActiveCell.FormulaR1C1 = _
            "=IFERROR((VLOOKUP(RC1,'availability'!C1:C23,5,FALSE)),"""")"
        Range("b51").AutoFill Destination:=Range("b51:b" & Range("a" & Rows.Count).End(xlUp).Row)
    
    
    End Sub
    Last edited by kelvin_9; Sep 19th, 2019 at 07:44 AM.

Some videos you may like

User Tag List

Tags for this Thread

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
  •