Question About Multi-Sheet Workbook with Worksheets As Formula Results

[perch98]

I have searched the web and searched these forums (as well as copied a few of the examples of these forums) - all to no avail. My problem is that the results only provide one worksheet as the result.... and here is my dilemma...

I have an Excel workbook of 'Security Groups'. In that workbook, I have created one tab that is my "User Dictionary". Since this is a Security-based workbook - I cannot share it, but I have made an example that very closely resembles the type of workbook I am working with. The user-dictionary has userIDs that populate the UserID columns on each Worksheet, and each worksheet resembles a Security Group. On the User Dictionary tab, I am needing a column that lists every Security group the user is a member of, and the majority of the users are a member of more than one group. My actual workbook has 12 worksheets, if that makes a difference in the formula - and my example workbook only has 4 worksheets, but the core setup is the same.

I want to thank everyone on this forum in advance - as I have learned SO much from everyone here! Y'all are an amazing group of people whom I have learned IMMENSELY from!

 

[Belleke]

See attacment if this works for you.
Attention Matrixformula. Close with Ctrl + Shift + Enter

 

[p45cal]

This was quite hard - I did a formula but it was long and unwieldy.
I've come up with a couple of different approaches, both involving queries but I'll only attach one here.
You'll see in cell E2 of the UserDic sheet a simplish formula:
=IFERROR(VLOOKUP(A2,UserIDGroupLookup,2,0),"No group")
copied down. It looks up the value in column A in the table UserIDGroupLookup which is on sheet List.
This lookup table needs refreshing from time to time (if the source data changes) with a click of the Refresh All icon in the Queries & Connections secion of the Data tab of the ribbon (or a right click of the lookup table followed by a click on Refresh).

This lookup table is the result of a query which uses your SheetList range converted to a table (this makes it easy to add/remove sheet names since the table will resize automatically) to decide which data to process.
Queries like to use tables so I converted all your Group sheets to Tables and gave each table the same name as the sheet it's on. It's really these table names that match the names in SheetList rather than the sheet names themselves.

As an aside, that lookup table in the List sheet could be tweaked to consolidate all the Group sheets and produce a table from them with the same headers as your table on the UserDic sheet and perhaps ultimately replace that table?

[The other solution involved a query too but didn't require the Group sheets data to be converted to tables - it was a bit more convoluted.]