If+index

[kelvin_9]

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

=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

 

[kelvin_9]

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

=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

 

[kelvin_9]

any help

is it impossible to do it as my formula is too many requirement

 

[brittymcsmartypants]

You might try something like this

=[COLOR=#333333]IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",[/COLOR][COLOR=#ff0000]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))[/COLOR][COLOR=#333333],[/COLOR][COLOR=#333333]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")))[/COLOR]

 

[brittymcsmartypants]

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

=IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",[COLOR=#ff0000]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))[/COLOR],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")))

 

[kelvin_9]

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

=IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",[COLOR=#ff0000]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))[/COLOR],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?

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