Filter from user selected drop down list

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello - I am trying to create a spreadsheet containing all the students in a
year group. I need to be able to filter this list by the maths group the
student is in. I have created a list box with the groups and I want the user
to select their group. Once selected, I would like the list to filter out
that set group. I realise this could be done via auto filter but I need it to
look more user friendly than that - how do I do this in excel?
Thanks!!

Maths Set 9X4

First Name Surname Set
Jo Bloggs 9X4
Bobb Bloggs 9X5
 
Here's a way to get there, using a listbox from the control toolbox ..

A sample implementation is available at:
http://cjoint.com/?dneaaBnqnl
Filter from another sheet using listbox.xls
(savefile.com is down at the moment)

Source table assumed in Sheet1,
cols A to C, with data in row2 down
(Key col "Set" is in col C)

In Sheet2,

In A2: =IF(Sheet1!C2="","",IF(Sheet1!C2=$E$1,ROW(),""))
(Leave A1 blank)

In B2:
=IF(ROW(A1)>COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL($A:$A,ROW(A1))))
Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of
data in Sheet1. Hide away col A for a neater look.

The listbox is created from the control toolbox,
with properties settings:

LinkCell: E1
ListFillRange: H4:H6

Within H4:H6 will be listed the unique values within the key col C in
Sheet1, eg:

9X4
9X5
9X6

Selecting say: "9X5" in the listbox will return the required filtered
results in Sheet2
 
Back
Top