Creating a second table based on certain conditions

[tadhgf]

Hi all,

I'm looking for help with a table I'm designing. I'm aware this has probably been asked numerous times in some form, but I'm yet to find a solution. I have one table where I record students behaviour data that can be entered as an integer. A simplified version lists student names and gives the tutor the option to insert how many times they didn't do homework during the week.

I'm trying to set up a second table on a separate sheet which is populated with student names that have 3 or more incidences of no homework. Based on the example above, the new table will have four names on the list, all which are highlighted in red above. This will create a table used for a detention list that will be populated with student names and the number of notes they have.

Thanks in advance for any help provided.

[#2]

Create a Pivot table...
Select a single cell in the table (e.g. a person's name)
Select "Insert" --> "Pivot Table" --> "OK" (It will create a Pivot Table on another sheet tab)
A new area should now appear on the right hand side of your spreadsheet
Drag the "Student Name" field into the "Rows" box (bottom left of the four boxes)
Drag the "Homework Notes" field into the "Filters" box (top left of the four boxes)
A filter box will appear on the actual Pivot Table with the text "Homework Notes" (Use that to filter and show only records with 3 or more instances) You should now see your 4 names.

[#3]

Thanks@Nauthstar I haven't used a Pivot Table before and that seems like it does the job. Is it possible to set up the pivot table so that you won't have to edit the filters every time results change? For example if a student gets 7 notes next week, he won't appear on the table after it is refreshed unless you update the filters.

My big problem is that this tool will not take off if it lacks ease of use. Some users will be against learning how to change filters. Is there a formula that can be used so that "if" someone has more than three notes the new list will be automatically populated?

Thanks again.

[#4]

Once you've set the filter(s) in the Pivot Table (e.g. <=4 or <7 etc.) they will stay that way until changed. All YOU or the user has to do is select a single cell in the pivot table, right hand mouse click and "Refresh All" Data and, if someone - who was displayed because they had only submitted 3 homework assignments, has now submitted 4 - they would no longer appear in the list.