How to fetch the Student Name List from Sheet 1 to Sheet 2 by choosing dropdown list

[vijubhau]

Hi Everybody...,
In my adjoining Excel Sheet (SortSubjectList.xlsx) on Sheet 1 i have the list of students with their respective subject.
And on Sheet 2 I make a data validation list in A1 and B1.

So, is it possible to fetch the list of whole students from Column B4 to number of total students on Sheet 2 as per the combination of A1 & B1??

[Czeslaw]

Formula

Please Login or Register  to view this content.

[JohnTopley]

In A4

=IF(B4="","",COUNTIF($B$4:B4,"<>"))

ib B4

=IFERROR(INDEX(Sheet1!$J$2:$J$165,AGGREGATE(15,6,ROW($A$1:$A$165)/(Sheet1!$B$2:$B$165=$B$1)/(Sheet1!$F$2:$F$165=$A$1),ROWS($1:1))),"")

[vijubhau]

No Sir... It is not working properly...

[JohnTopley]

.. So what is not working ????

Post file with "error" results.

[bebo021999]

b4:

Please Login or Register  to view this content.

a4:

Please Login or Register  to view this content.

[vijubhau]

As I copied the above formula in in A4 and B4 and drag it till B170.. It is not show the the list of students in Column B as per the combination of A1 & B1..

[bebo021999]

As I copied the above formula in in A4 and B4 and drag it till B170.. It is not show the the list of students in Column B as per the combination of A1 & B1..

I guess my post in #6 have not been updated, till your last post...
Hope it works!

[vijubhau]

I guess my post in #6 have not been updated, till your last post...
Hope it works!

No.. Sir.. Whenever I change the value of A1 & B1.. It it showing nothing in B4 to Last B Column...

[Fluff13]

Are you still using Excel 2007? If so you don't have the Aggregate function.

[Czeslaw]

Formula

Please Login or Register  to view this content.

[JohnTopley]

If you have 2007

=IFERROR(INDEX(Sheet1!$J$2:$J$166,SMALL(IF((Sheet1!$B$2:$B$165=$B$1)*(Sheet1!$F$2:$F$165=$A$1),ROW($A$2:$A$165)-ROW($A$2)+1,""),ROWS($1:1))),"")

Enter with Ctrl+Shift+Enter

If NOT 2007,please update your profile

[vijubhau]

Thanks a Lot Sir... But there is a little bit problem.. and the problem is that, the formula is taking only the value from column F [I Want all value from Column C to Column I] from Sheet1..
As I choose the different from dropdown in Sheet2 From A1 or B1, the result is showing Blank

[JohnTopley]

Your data is "inconsistent": for example "SOC" appears in Columns E and F as does "POL" so this means searching ALL columns C:G to find any match.

This checks columns E & F

=IFERROR(INDEX(Sheet1!$J$2:$J$165,SMALL(IF((Sheet1!$B$2:$B$165=$B$1)*((Sheet1!$E$2:$E$165=$A$1)+(Sheet1!$F$2:$F$165=$A$1)),ROW($A$2:$A$165)-ROW($A$2)+1,""),ROWS($1:1))),"")