performing advanced filter to other sheet

Microsoft Office access 2007 (full produ...
July 7, 2010 at 02:28:13
Specs: Windows XP
Hi all,
I have a worksheet with c. 1600 rows, with Columns A-H.

I want to be able to perform an advanced filter from 'Student Input' (the sheet with all the rows) to 'Report' (the sheet I want the results to be shown in)

I need to be able to search for multiple criteria and dont have the option of being able to place a table with the original 'Student Input' data on the Report sheet as it is simply too big.

My columns are:

A: Name
B: School
C: Class
D: Dept
E: Year Group
F: Filed

If possible, I'd like to be able to filter simultaneously, up to three of these categories.

Any help would be greatly appreciated.

Many thanks

See More: performing advanced filter to other sheet

Report •

July 7, 2010 at 04:22:25
As you may know, Advanced Filtering can not push data to another sheet. However, it can pull it.

Switch to your Report sheet and open the Advanced Filter dialog box from that sheet.

In the List Range field, put something like:

'Student Input'!$A$1:$A$1600

Then set the rest of your options.

Does that help?

Report •

July 7, 2010 at 04:38:37
Hi Derby,
Thanks for that, knowing it helps, but I think it'll take a bit of poking around to actually getting it working the way im hoping, I'm unsure of how the 'Criteria' selection is linked to howI actually select what I'm looking for if you get my drift - i.e how can i manipulate the criteria to allow me to look for a specific class, in a specific year in a specific dept? that parts gets me - like do I just give the top row as my criterion then assume that each of the columns in report correspond to the columns in Student Input?

Thanks again

Report •

July 7, 2010 at 05:22:41
I'm not a filtering expert, so I can't give you specifics as far as setting up your criteria, but I know that you use a Criteria Range to enter the Criteria you wanrt to filter on.

Check out this site to see if it answers your questions:

Report •

Related Solutions

July 7, 2010 at 05:28:29

Criteria is a two row range of cells, with the upper row containing the names of the columns used in your source data and the lower row containing the data to be searched for. So upper row might be SchoolName and underneath it is St. Jude's

You can have a destination range which contains the column headings that you wish to display.

Once you have it working used named ranges for Source data range, Criteria range and Destination range. This will save navigating and selecting each range.


Report •

July 7, 2010 at 05:53:00
re: Criteria is a two row range of cells

Actually, as shown at the Contextures site, the criteria range can be 3 (or more?) rows.

See here:

Report •

July 7, 2010 at 06:09:00

I was just going for the basics.

You are quite right - there are lots of options for making the filters more complex.

I do wonder if the use of Advanced Filter which requires a series of manual steps every time is the best way to go.

I use the database functions which deliver results as soon as the criteria are changed - no selecting ranges etc. etc.


Report •

July 7, 2010 at 07:38:45
Thanks guys,
I seem to be getting to the stage of bringing up the entire table from 'Student Input' to 'Report', but cant seem to get my head around the actual filtering of the data on the page, I;ve followed the video but I'm obviously doing something wrong here. Im trying to filterthe data depending on 3 of the criterion, I followed that link to the letter but to no avail...frustrating!

Report •

July 8, 2010 at 01:25:04
Ok guys, Im having no joy, is there a way I can run a filter from VB based on combobox choices?

Report •

July 8, 2010 at 07:30:32

May I suggest that you try a small example in a new workbook, just to get the hang of it:

On Sheet1 enter this:

	A	B	C	D
1	Name	School	Class	Dept
2		A	1	X
4	Name	School	Class	Dept
5	1	A	1	X
6	2	A	2	Y
7	3	B	1	Z
8	4	B	2	X
9	5	C	1	Y
10	6	C	2	Z
11	7	A	1	X
12	8	A	2	Y
13	9	B	1	Z
14	10	B	2	X
15	11	C	1	Y
16	12	C	2	Z
17	13	B	2	X
18	14	C	1	Y
19	15	C	2	Z
20	16	A	1	X
21	17	A	2	Y
22	18	B	1	Z
23	19	C	2	Z
24	20	B	2	X

Rows 1 and 2 are used for the criteria.
The names in Row 1 must be identical to those in row 4 at the top of the data table.

On Sheet 2 enter this, to determine which columns from the filtered data will be displayed.

	A	B
1	Name	Dept

On Sheet 2 select Filter - Advanced Filter
Select Copy to another location

List range is:		Sheet1!$A$4:$D$24
Criteria range is:	Sheet1!$A$1:$D$2
Copy to is:		Sheet2!$A$1:$B$1

Hit OK

The result on Sheet2 is:

	A	B
1	Name	Dept
2	1	X
3	16	X

Once this works, you can apply the principles to your 'real' data.


Report •

Ask Question