Excel filter to create sheets 2

hi,

Unless you write code, it will take manually adding a sheet for each student.

If you have 2007+, make your student table a Structured Table! Otherwise dynamically name the range of your table Database cuz, pivottable kind of like that name, it seems.

You could use a pivottable on each sheet (without the aggregations) This seems to work in 2007+

You could add a PARAMETER querytable on each sheet with the student's name in A1 as the parameter value and query that student's data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

Have a look at Advanced filter and make sure that you use named ranges.

First Advanced filter with no criteria, to a new location, with a single cell in the Extract to range and Unique values only ticked. This will give you a list of all the names and you will want to create a separate sheet for each.

I would use VBA to loop through this list doing an advanced filter using each Name as the extract criteria. The ExtractTo range would be on a separate sheet. After each extract copy the sheet and rename it.

OR much easier with your example

Create a pivot table with Name in the page field. Then use ShowReportFilterPages will generate a page for each Name. The same function is available in 2003 and earlier but called, I think, ShowAllPages

Gavin

@Gavin

Where's the setting for ShowReportFilterPages. Can't seem to find in my 2007

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

Thanks for the suggestions, I will try them soon.
I asked this question here because if I can do it ‘manually’ first, then I can code it in VBA (which is my goal). I have many reports like that and I do them probably the ‘hard’ way now (in VBA, data from Oracle, program in VB 6, soon to be VB.NET), so if I can find a better way (simply by dumping all of that in Excel and say: ‘You do it’) that would be great


Have fun.

---- Andy

Hi Skip. I have 2010 (but still finding my way around). PivotTable Tools ==> PivotTable group ==> Options


Gavin

The pivot table solution is great and by far the quickest. Once all sheets are created (seconds) then select them all and adjust column width formatting etc - don't cycle through each one unless you really have to.

Gavin

Wow! Okay Gavin!!! Great TIP!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

Gavin

If Skip likes it, I want to know about this, too. But could any of you ‘dumb it down’ for me, please? For somebody who knows very little about Excel. Some step-by-step instruction would be great.


Have fun.

---- Andy

Andrzejek:
Get some data
Create a pivot table
Have at least one field as a PageField (Excel2003 Name) / ReportFilter (Excel2010 name)

Now in Excel 2010 you click on PivotTable Tools at the top and it opens the PivotTable ribbon
You click on the arrow beside "Options" and choose "ShowReportFilterPages"
Here is a screenshot




In Excel2003 then you need the pivotTable toolbar and ShowAllPages is the last option on it.

Gavin

Once all sheets are created (seconds) then select them all and adjust column width formatting etc - don't cycle through each one unless you really have to.

That was the Pivot table solution. The advanced filter one is good too but you MUST use named ranges to get the results on a different sheet.

So call your data "Database"
Your criteriarange "Criteria"
Your Extract range (list of headings to extract) "Extract"

To get a list of all the names in your workbook:
Step1. Criteria range is blank - you want all records
Step2. Extract range is a single cell containing the title of your column "Name"
Step3. Tick the Unique Records Only box



To create a sheet for the first name on the list:
Step1. Criteria range is two cells. Name, and immediately below that the first name from your list
Step2. Extract range is in a single row and contains all your headings
Step3. Do NOT tick Unique records only

Copy the sheet.
Return to the first sheet
Modify the criteria for the next name and repeat




Gavin

Just a note here
I did not respond to this post yet because I am trying to ‘decipher’ the steps given. I even asked more knowledgeable Excel person about pivot tables, named ranges, ReportFilters, criteriaranges, etc. etc. They are still ‘digesting’ this information and promised to get back to me.

Like I said, I am just a ‘simple’ Excel user and I program against Excel in very limited scope. I know the best way would be to just give users Filter so they could filter their ‘stuff’ on one Sheet, and that would be it. But ... :-(


Have fun.

---- Andy

Hey, no worries Andrzejek. The more detailed instructions may vary depending on what version of Excel you use - so maybe you could tell us that? I would focus on the pivottable solution as pivots are wonderful versatile tools ==> well worth learning about.




Gavin

It is Excel 2010, all users have the same version. Excel is used to create reports for users.

Have fun.

---- Andy

First Lets Create a database to play with
1. Open a new workbook

2. Copy the following and paste it into cell A1
Fruit,Qty,Quality
orange,4565,a
apple,17818,a
orange,9478,a
banana,24480,a
orange,17713,b
apple,21191,b
orange,22475,b
banana,7296,c

3. The data you just passed into cells A1 to A9 should still be selected.
Date==>Text to Columns==>Delimited=>[Next]==>Comma==>[Next]==>[Finish]

Now We will name the range
4. Select Cell A1==>Ctrl-A (extends the selection to the CurrentRegion

5. Type Database in the Name box then press enter.

6. Insert==>PivotTable==>now replace the range that Excel guessed with Database==>[OK}

7. Drag Fruit to the ReportFilter area, Quality to the Row Labels and Quantity to the values area

You have a PivotTable 8. You will see that you can use the Report Filter in cell B1 to select just one type of fruit

Now lets create a separate sheet for each fruit:
9. When you are clicked on a cell within the PivotTable the PivotTable Ribbon is available. As previously posted you click on PivotTable Tools at the top and it opens the PivotTable ribbon
You click on the arrow beside "Options" and choose "ShowReportFilterPages"
Here is a screenshot

Now lets format all the sheets in one go
10. Right-Click on a sheet tab and choose “Select all “ Then do some formatting – change column widths. Maybe insert a Couple of rows above the Pivot and type your title and the date.

11. Right-Click on a sheet tab and choose “Ungroup Sheets “

Put each sheet into a separate workbook
12. First a decision – do you want each workbook owner to be able to change the report filter to see the results for the other (in this case) fruit? If not then do not save the data with the pivot: Untick “Save Source Data with File”.

13. Now right-click on thesheet tab and follow the options to move the sheet to a new workbook.

14. Save and close the workbook containing your data. This ensures that the results workbook(s) know the full path to the data workbook.

15. Save your results workbooks

When you come to update the data…16. The pivot can be refreshed to get the latest data but ONLY by someone with access rights to the data workbook.

17. When you change the data in the Data workbook make sure that you update the named range in vba …..Range(“Database”).CurrentRegion.Name=”Database” will do the trick.

18. So long as you don’t use a dynamic named range then you can refresh your results workbooks even when the Data workbook is closed.


Gavin

Thank you Gavin (and Skip), I really appreciate your help. I would give you more than one star, if I could.
This step-by-step is what I’ve been looking for. Easy to follow, just my style (and speed). I will use it some day, I am sure.


Have fun.

---- Andy

Hi Gavin looks great. Just one thing I couldn't get:

do you want each workbook owner to be able to change the report filter to see the results for the other (in this case) fruit? If not then do not save the data with the pivot: Untick “Save Source Data with File”.

Click to expand...

I am using MS Office Professional Plus 2010.

Many thanks,
D€$

RightClick over pivottable, Options, Data, Untick “Save Source Data with File”

Gavin