Forum Discussion
Creating a second table based on certain conditions
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 ...
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.
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.
ThanksNauthstar 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.
- 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.
