Populating multiple sheet names using Index, Match and Indirect

[Darren_workforce]

Hello,

My file contains 10 worksheets, each named after our unique phone queues. Each tab has a list of skilled agent names down Col A. On my Lookup tab, I have the formula below which references the name entered in B4 and then what I want it to do is list ALL worksheets that the name is located under.

EX: CustomerService, ConciergeClient, TechSupport.
EX: If Joe Smith was only skilled to CustomerService and ConciergeClient, the resulting formula should display those 2 queues into Lookup tab B7 where I have the formula setup.

However, the issue is that only the first worksheet name is populating. What do I need to tweak to make the formula successful?

=IFERROR(INDEX(SheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&SheetList&"'!$A$1:$A$50"),B4)>0),0)),"")

Thank you in advance!!

 

[Fluff]

Are the 10 sheets all consecutive? If so what is the name of the 1st & last sheet?

 

[Darren_workforce]

Are the 10 sheets all consecutive? If so what is the name of the 1st & last sheet?

The first sheet within 'SheetList' is OnlineAssistance. The last sheet is CSTMailings. The order of the sheets in the group do match with the order they appear at the bottom. However, they are not in alphabetical order.

There is an 11th sheet but it's not part of the 'SheetList' group. It's just Agent Lookup if that matters.

 

[Fluff]

Ok, in the Name manager create a new name (I called it ShtNames) & in the refers to box put

=TOCOL(TEXTAFTER(GET.WORKBOOK(1)&T(NOW()),"]",-1))

Then in the sheet you can use this formula

=LET(s,DROP(TAKE(ShtNames,XMATCH("CSTMailings",ShtNames)),XMATCH("OnlineAssistance",ShtNames)-1),v,VSTACK(OnlineAssistance:CSTMailings!A1:A50),t,TOCOL(IF(SEQUENCE(,ROWS(v)/ROWS(s)),s)),TOROW(FILTER(t,v=B4)))

Which will spill the sheet names along the row.

The workbook will need to be saved as an xlsm or xlsb & macros will need to be enabled.

 

[Darren_workforce]

Ok, in the Name manager create a new name (I called it ShtNames) & in the refers to box put

=TOCOL(TEXTAFTER(GET.WORKBOOK(1)&T(NOW()),"]",-1))

Then in the sheet you can use this formula

=LET(s,DROP(TAKE(ShtNames,XMATCH("CSTMailings",ShtNames)),XMATCH("OnlineAssistance",ShtNames)-1),v,VSTACK(OnlineAssistance:CSTMailings!A1:A50),t,TOCOL(IF(SEQUENCE(,ROWS(v)/ROWS(s)),s)),TOROW(FILTER(t,v=B4)))

Which will spill the sheet names along the row.

The workbook will need to be saved as an xlsm or xlsb & macros will need to be enabled.

Is it possible to convert that to spill down a single column instead of across rows?

 

[Fluff]

Yup. you can use

=LET(s,DROP(TAKE(ShtNames,XMATCH("CSTMailings",ShtNames)),XMATCH("OnlineAssistance",ShtNames)-1),v,VSTACK(OnlineAssistance:CSTMailings!A1:A50),t,TOCOL(IF(SEQUENCE(,ROWS(v)/ROWS(s)),s)),FILTER(t,v=B4))

 

[Darren_workforce]

Yup. you can use

=LET(s,DROP(TAKE(ShtNames,XMATCH("CSTMailings",ShtNames)),XMATCH("OnlineAssistance",ShtNames)-1),v,VSTACK(OnlineAssistance:CSTMailings!A1:A50),t,TOCOL(IF(SEQUENCE(,ROWS(v)/ROWS(s)),s)),FILTER(t,v=B4))

Sorry - impromptu meeting came up so response was a little late. @Fluff you're amazing as always!! Thank you for the assistance on this one.

 

[Fluff]

You're welcome & thanks for the feedback.