Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP: Use the autofilter result on one workbook to filter the next list on another workbook
Hi All,
Is it possible to extract the filtered result after using the AutoFilter and use this list to be the filter criteria on another worksheet. So for example: on the first worksheet - filter for all failed (grade <= D+) students on one paper on the next worksheet - get the filtered list on the 1st worksheet and automatically use this list to custom filter the next list, so it will display all the failed students past grades history Can this be done? Cheers Kathy |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP: Use the autofilter result on one workbook to filter the next
One interpretation on your post, and a possible way to automate your
underlying intents is illustrated in this sample: http://www.savefile.com/files/1035931 Extract failure list fr x n past grades history fr y.xls In sheet: z, To "filter" the failure list from sheet: x, I used the underlying papers' marks (ie numbers) rather than alphabetic grades, and relied on looking up unique student IDs (rather than names) to match/extract their historical records from the masterlist in sheet: y ----- Here's the set-up descript Assume that sheet: x = Listing for latest paper3 (marks in col C, from row2 down) y = MasterList for the subject (Papers 1 to 3) [Student IDs and names are assumed in cols A and B, from row2 down in both sheets x and y] In a new sheet: z, In A2: =IF(x!C2="","",IF(x!C2<55,ROW(),"")) Marks < 55 is the assumed failure criteria. Leave A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) Copy B2 to C2, fill down to cover the max data extent expected in sheet: x. Cols B and C will extract the list of IDs & names of students who "failed" in x (latest paper3), ie those who scored less than 55 marks, with all lines neatly bunched at the top. Then in D2: =IF(ISNA(MATCH($B2,y!$A:$A,0)),"",INDEX(y!C:C,MATC H($B2,y!$A:$A,0))) Copy D2 to I2, fill down to populate. Cols D to I will extract the past grades history results for all these failed students from the masterlist in sheet: y. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Kathy Houtami" wrote: Hi All, Is it possible to extract the filtered result after using the AutoFilter and use this list to be the filter criteria on another worksheet. So for example: on the first worksheet - filter for all failed (grade <= D+) students on one paper on the next worksheet - get the filtered list on the 1st worksheet and automatically use this list to custom filter the next list, so it will display all the failed students past grades history Can this be done? Cheers Kathy |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP: Use the autofilter result on one workbook to filter the
Typo in this line:
Copy B2 to C2, fill down to cover the max data extent expected in sheet: x. It should read as: Copy B2 to C2. Select A2:C2, fill down to cover the max data extent expected in sheet: x. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP: Use the autofilter result on one workbook to filter the
Thank you Max, I will give your suggestion ago and update you
On Sep 7, 2:48 pm, Max wrote: Typo in this line: Copy B2 to C2, fill down to cover the max data extent expected in sheet: x. It should read as: Copy B2 to C2. Select A2:C2, fill down to cover the max data extent expected in sheet: x. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP: Use the autofilter result on one workbook to filter the
Hi Max
I have tried your solution, unfortunately not quite suited for my problem. What I have is 2 worksheets on the same workbook, both worksheets has exactly the same data. It has ID column (number), Name, Grade (but I have also added a numeric column to convert the grade so I can easily do filter on this, and once the result is produced this numeric column will be hidden before it goes to print), papers (which will list any papers has been taken by each student - like your y worksheet, but rather then listing the papers across, it is all listed down). ID Name Marks Grade Paper 1001 Nam1 68 C P1 1002 Nam2 78 B P1 1003 Nam3 42 D P1 1004 Nam4 86 A P1 1005 Nam5 65 C P1 1006 Nam6 66 C P1 1001 Nam1 55 C P2 1002 Nam2 60 C P2 1003 Nam3 58 C P2 1004 Nam4 76 B P2 1005 Nam5 43 D P2 1006 Nam6 67 C P2 1001 Nam1 39 E P3 1002 Nam2 89 A P3 1003 Nam3 44 D P3 1004 Nam4 73 B P3 1005 Nam5 40 D P3 1006 Nam6 52 D+ P3 1001 Nam1 68 C P5 1002 Nam2 78 B P8 1004 Nam4 73 B P9 .. . . . . .. . . . . .. . . . . One the first worksheet I have put AutoFilter on the column headers, so I can filter by Paper and custom filter on the Marks column to find all failed students for one paper. And I would like sheet2 to automatically pick up the ID numbers from the filtered sheet1, and filter sheet2 list to display only those ID listed on sheet1 and sort by the Paper. Cheers Kathy On Sep 10, 2:35 pm, Kathy Houtami wrote: Thank you Max, I will give your suggestion ago and update you On Sep 7, 2:48 pm, Max wrote: Typo in this line: Copy B2 to C2, fill down to cover the max data extent expected in sheet: x. It should read as: Copy B2 to C2. Select A2:C2, fill down to cover the max data extent expected in sheet: x. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik ---- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP: Use the autofilter result on one workbook to filter the
Try this revised set-up which should deliver it ..
Illustrated in this sample (based on your posted sample data): http://www.savefile.com/files/1046220 Extract fail IDs by paper_passmark n list full history in new sht.xls Source data as posted assumed in sheet: x, cols A to E In a new sheet: y, Select the paper & input the paper's passing mark in K4:K5 (This sets the "filter" criteria for the source data in x) In A2: =IF(x!C2="","",IF(AND(x!C2<$K$5,x!E2=$K$4),ROW()," ")) Leave A1 blank In B2: =IF(ROWS($1:1)COUNT(A:A),"",INDEX(x!A:A,SMALL(A:A ,ROWS($1:1)))) In C2: =IF(ISNUMBER(MATCH(x!A2,B:B,0)),ROW(),"") Leave C1 blank In D2: =IF(ROWS($1:1)COUNT($C:$C),"",INDEX(x!A:A,SMALL($ C:$C,ROWS($1:1)))) Copy D2 to H2. Select A2:H2, copy down to cover the max expected extent of source data in x. Col B will return the list of Fail IDs for the paper/mark set in K4:K5. Cols D to H will return the required full history of the Fail IDs. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Kathy Houtami" wrote in message ps.com... Hi Max I have tried your solution, unfortunately not quite suited for my problem. What I have is 2 worksheets on the same workbook, both worksheets has exactly the same data. It has ID column (number), Name, Grade (but I have also added a numeric column to convert the grade so I can easily do filter on this, and once the result is produced this numeric column will be hidden before it goes to print), papers (which will list any papers has been taken by each student - like your y worksheet, but rather then listing the papers across, it is all listed down). ID Name Marks Grade Paper 1001 Nam1 68 C P1 1002 Nam2 78 B P1 1003 Nam3 42 D P1 1004 Nam4 86 A P1 1005 Nam5 65 C P1 1006 Nam6 66 C P1 1001 Nam1 55 C P2 1002 Nam2 60 C P2 1003 Nam3 58 C P2 1004 Nam4 76 B P2 1005 Nam5 43 D P2 1006 Nam6 67 C P2 1001 Nam1 39 E P3 1002 Nam2 89 A P3 1003 Nam3 44 D P3 1004 Nam4 73 B P3 1005 Nam5 40 D P3 1006 Nam6 52 D+ P3 1001 Nam1 68 C P5 1002 Nam2 78 B P8 1004 Nam4 73 B P9 . . . . . . . . . . . . . . . One the first worksheet I have put AutoFilter on the column headers, so I can filter by Paper and custom filter on the Marks column to find all failed students for one paper. And I would like sheet2 to automatically pick up the ID numbers from the filtered sheet1, and filter sheet2 list to display only those ID listed on sheet1 and sort by the Paper. Cheers Kathy |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
HELP: Use the autofilter result on one workbook to filter the
Clarification for this line:
Select the paper & input the paper's passing mark in K4:K5 In the sample construct, there's a simple DV list created in K4 to select either: P1, P2 or P3 (the paper) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFilter settings in shared workbook | Excel Worksheet Functions | |||
AutoFilter settings in shared workbook | Excel Worksheet Functions | |||
Product Price List with 14k records, filter w/out using AutoFilter | Excel Worksheet Functions | |||
Autofilter on protected workbook | Excel Discussion (Misc queries) | |||
Can I AutoFilter an entire workbook? | Excel Discussion (Misc queries) |