SOLVED

# IFERROR +1

Copper Contributor

# IFERROR +1

I am trying to create a spreadsheet to show me who the duty person is on a particular day. I have managed to get my formula to show me the name of the days' duty person however some days there are two people on duty and I have tried to add +1 into my formula however this is now just showing me the next name in the list, not the next duty person.

Sheet 1 =IFERROR(INDEX(Sheet2!\$C\$5:\$C\$18,MATCH("Duty",Sheet2!D5:D18,0)),"")

 W/C 01/01 Mon Tue Wed Thu Fri 01-Jan 02-Jan 03-Jan 04-Jan 05-Jan Name A Person A Person 7 A Person 3 A Person 2 A Person 5 A Person 2 A Person 2

Sheet 2

 Building ID Name Mon Tue Wed Thu Fri 01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 1 A Person Duty AM/PM AM/PM 1 A Person 2 AM/PM Duty 1 A Person 3 Duty 1 A Person 4 AM/PM AM/PM 1 A Person 5 Duty Duty 1 A Person 6 AM/PM 1 A Person 7 Duty Duty 1 A Person 8 AM/PM 1 A Person 9 AM/PM Duty 1 A Person 10 Duty Duty 1 A Person 11 AM/PM 1 A Person 12 AM/PM AM/PM 1 A Person 13 AM/PM 1 A Person 14 AM/PM AM/PM
3 Replies
best response confirmed by AjayneA (Copper Contributor)
Solution

# Re: IFERROR +1

@AjayneA if you have 365 then you can use:

``=FILTER(Sheet2!\$C\$5:\$C\$18,"Duty"=Sheet2!D5:D18,"")``

and it will 'spill' the list of all people listed on 'duty'

# Re: IFERROR +1

Thank you so much! This has been vexing me for over a week.

# Re: IFERROR +1

Glad to help. Glad it works for you. and hopefully next time you won't wait a week before reaching out :)
1 best response

Accepted Solutions
best response confirmed by AjayneA (Copper Contributor)
Solution

# Re: IFERROR +1

@AjayneA if you have 365 then you can use:

``=FILTER(Sheet2!\$C\$5:\$C\$18,"Duty"=Sheet2!D5:D18,"")``

and it will 'spill' the list of all people listed on 'duty'