filtered lists based on another cell value

[chubbchubb]

Ok I am trying to return a list of students names and their grades dependent on whether they are in a certain class or not. The idea is that I have a dropdown list for a teacher to pick a class and it will then show their list of students with the different grades.

I have tried adapting an INDEX formula but can't get it to work.

[Tony Valko]

See this:

http://www.excelforum.com/tips-and-t...ml#post3376007

[chubbchubb]

That is brilliant thank you. Going to make it more complicated now though.
this is the formula I am using which works great. =IFERROR(INDEX(Data!A:A,SMALL(IF(Data!I:I=$D$1,ROW(Data!I:I)),ROWS($A$3:A3))),"")

But I want the search critiera to look more like this =IFERROR(INDEX(Data!A:A,SMALL(IF(Data!I:I="*"&$D$1&"*",ROW(Data!I:I)),ROWS($A$3:A3))),"") so that the cell just has to contain part of the text. but when I do this it just returns a blank

any ideas

[Tony Valko]

You should avoid using entire columns as range references in array formulas. Use smaller specific ranges.

Try it like this...

=IFERROR(INDEX(Data!A:A,SMALL(IF(ISNUMBER(SEARCH($D$1,Data!I$2:I$100)),ROW(Data!I$2:I$100)),ROWS($A$3:A3))),"")

Don't forget: array entered!

[chubbchubb]

thanks very much, bit of tweaking and it is working

[Tony Valko]

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

[chubbchubb]

One last thing on this one. I want to be able to sort the order of the results. for example I want them to be alphabetical . I have put a filter on each of the headings but when I use the sort option it starts to sort them but then immediately reverts back to the original order. Is there anyway I can sort this?

[Tony Valko]

It may be possible but it would be mind boggling complicated.

Can you post a SMALL sample file and show us what results you expect?

A SMALL file will have about 20 rows worth of data.