Get sub group data based on main group

[sujittalukde]

The attached file contains two sheets.
First sheet is the Main sheet which contains the Main Group and Sub group data.
Second sheet is the "Report" sheet where cell B! contains a drop downlist.

Now I want to show the name of the members based on cell B1 value starting from cell B3. for eg., if I select "Marketing" in cell B1 then all the members name for "Marketing" should display in cell B3,B4 and so on. I will put the formula till row 50 as at most 50 records could be there.

Help needed to draw the formula for the same. (No macro)

[p45cal]

Would placing an autofilter on the first column of the Main sheet not do this for you, as in the attached?

[sujittalukde]

Hi,
Thanks for the reply.
But this is a part of my small project where the final output sheet ie Report will be sent to higher authority and also data are complex in actual. This is simplified version to know the formula.
So autofilter wouldnot work.

[Danny]

Here is the same workbook with a macro that will run automatically every time the dept choice changes. I don't know how to do it with just formulas... Hope this helps.

[mikerickson]

Here's a spreadsheet solution.
In Report! B3:B11, put the array formula
=INDEX(Main!$B:$B,SMALL(IF(Main!$A2:$A12=B1,ROW(Main!$A2:$A12),999),ROW($A1 :$A120)))&""

If you have a dynamic named range
Name: Departments
RefersTo: =OFFSET(Main!$A$2,0,0,COUNTA(Main!$A:$A)-1,1)

This array formula in D3 : D11 will react to the entry in D1

=INDEX(Main!B:B,SMALL(IF(Departments=D1,ROW(Departments),999),ROW($A1:$A120 )),1)&""

Both these formulas need to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac)