Thread: If+index Thanks: 0 Likes: 0

1. 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. Re: If+index

Originally Posted by 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
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. Re: If+index

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

4. 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. Re: If+index

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. Re: If+index

Originally Posted by brittymcsmartypants

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```