Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.programming
|
|||
|
|||
Use the autofilter result on one workbook to filter the next list on another workbook
Yes, it can be done. I have built a report that part of it does something
similar with a filtered list. Because it is all automated, the second list is filtered by each item from the first list so fast there is no time to read the results. So what I did was to transfer the results to a third sheet that could be named "Failed History" in your case. That sheet would list each failed student from the first list, and their grade history from the second list, with a blank row between each student's history, as a separator. We would need details of your workbook(s) in order to help you get started. What is your experience level in writing VBA code? None, Some, Intermediate, Advanced, Developer? Mike F "Kathy Houtami" wrote in message ups.com... 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.programming
|
|||
|
|||
Use the autofilter result on one workbook to filter the next list on another workbook
Hi Mike
What kind of detail do you need on my work book. I have an intermediate - advance level on VBA programming, though only in MS Access, but I assume not too much difference. This is my first attempt on try to program in excel VBA. Cheers Kathy On Sep 7, 11:00 pm, "Mike Fogleman" wrote: Yes, it can be done. I have built a report that part of it does something similar with a filtered list. Because it is all automated, the second list is filtered by each item from the first list so fast there is no time to read the results. So what I did was to transfer the results to a third sheet that could be named "Failed History" in your case. That sheet would list each failed student from the first list, and their grade history from the second list, with a blank row between each student's history, as a separator. We would need details of your workbook(s) in order to help you get started. What is your experience level in writing VBA code? None, Some, Intermediate, Advanced, Developer? Mike F "Kathy Houtami" wrote in message ups.com... 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- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use the autofilter result on one workbook to filter the next list on another workbook
How the data is laid out in the two tables and what kind of data are in the
tables. Is the name entered in one cell or two and how? LastName, FirstName? This is important because the names are how we are going to filter the historical list. The grade values are important to filter the first list. Are they numerical values or are they as your example; A+, A, A-, etc. If they are letters then the formula (grade <= D+) is meaningless to Excel and we would need to assign some value to each grade. If you were to sort lettered grades in ascending order you would get: A, A-, A+. Do you see why I am asking this? An easier alternative to writing a description is to email a copy of the workbook to me. Mike F "Kathy Houtami" wrote in message ps.com... Hi Mike What kind of detail do you need on my work book. I have an intermediate - advance level on VBA programming, though only in MS Access, but I assume not too much difference. This is my first attempt on try to program in excel VBA. Cheers Kathy On Sep 7, 11:00 pm, "Mike Fogleman" wrote: Yes, it can be done. I have built a report that part of it does something similar with a filtered list. Because it is all automated, the second list is filtered by each item from the first list so fast there is no time to read the results. So what I did was to transfer the results to a third sheet that could be named "Failed History" in your case. That sheet would list each failed student from the first list, and their grade history from the second list, with a blank row between each student's history, as a separator. We would need details of your workbook(s) in order to help you get started. What is your experience level in writing VBA code? None, Some, Intermediate, Advanced, Developer? Mike F "Kathy Houtami" wrote in message ups.com... 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- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use the autofilter result on one workbook to filter the next list on another workbook
Hi Mike
I have 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). 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, 10:55 pm, "Mike Fogleman" wrote: How the data is laid out in the two tables and what kind of data are in the tables. Is the name entered in one cell or two and how? LastName, FirstName? This is important because the names are how we are going to filter the historical list. The grade values are important to filter the first list. Are they numerical values or are they as your example; A+, A, A-, etc. If they are letters then the formula (grade <= D+) is meaningless to Excel and we would need to assign some value to each grade. If you were to sort lettered grades in ascending order you would get: A, A-, A+. Do you see why I am asking this? An easier alternative to writing a description is to email a copy of the workbook to me. Mike F"Kathy Houtami" wrote in message ps.com... Hi Mike What kind of detail do you need on my work book. I have an intermediate - advance level on VBA programming, though only in MS Access, but I assume not too much difference. This is my first attempt on try to program in excel VBA. Cheers Kathy On Sep 7, 11:00 pm, "Mike Fogleman" wrote: Yes, it can be done. I have built a report that part of it does something similar with a filtered list. Because it is all automated, the second list is filtered by each item from the first list so fast there is no time to read the results. So what I did was to transfer the results to a third sheet that could be named "Failed History" in your case. That sheet would list each failed student from the first list, and their grade history from the second list, with a blank row between each student's history, as a separator. We would need details of your workbook(s) in order to help you get started. What is your experience level in writing VBA code? None, Some, Intermediate, Advanced, Developer? Mike F "Kathy Houtami" wrote in message roups.com... 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use the autofilter result on one workbook to filter the next list on another workbook
OK Kathy, I have something for you to try out. This code assumes that both
lists start in A1 and there is nothing else on the sheets. That would mess up my row counts. There are 3 routines. The first one does the bulk of the work. You could put a CommandButton on sheet1 and assign this macro to it. It will copy the filtered data in column A to 1 row below your list, in column C. It will use that list as the Advanced Filter criteria on sheet2 and then display sheet2 for you. On sheet2 you can have another CommandButton and assign to it the macro named "UnfilterList2". That macro will show all data on sheet2, go back to sheet1 and delete the criteria range. You are now ready to filter list1 all over again. Option Explicit Public rng As Range Sub FilterList2byList1() Dim LRow As Long, FiltRow As Long Dim LRow2 As Long, FiltRow2 As Long LRow = Sheet1.UsedRange.Rows.Count FiltRow = Cells(Rows.Count, 1).End(xlUp).Row Set rng = Range("A1:A" & FiltRow) rng.Copy Cells(LRow + 2, 3) Cells(LRow + 2, 3).CurrentRegion.Name = "Criteria" Set rng = Cells(LRow + 2, 3).CurrentRegion LRow2 = Sheet2.UsedRange.Rows.Count FiltRow2 = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row If LRow2 FiltRow2 Then Sheet2.ShowAllData 'FiltRow2 = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row Sheet2.Cells(1, 1).CurrentRegion.Name = "Database" Range("Database").AdvancedFilter _ Action:=xlFilterInPlace, _ CriteriaRange:=Range("Criteria") Sheet2.Activate End Sub Sub UnFilterList2() Sheet2.ShowAllData Cleanup End Sub Sub Cleanup() Sheet1.Activate Sheet1.Range(rng, rng(-1, 1)).Rows.EntireRow.Delete End Sub Mike F "Kathy Houtami" wrote in message s.com... Hi Mike I have 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). 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, 10:55 pm, "Mike Fogleman" wrote: How the data is laid out in the two tables and what kind of data are in the tables. Is the name entered in one cell or two and how? LastName, FirstName? This is important because the names are how we are going to filter the historical list. The grade values are important to filter the first list. Are they numerical values or are they as your example; A+, A, A-, etc. If they are letters then the formula (grade <= D+) is meaningless to Excel and we would need to assign some value to each grade. If you were to sort lettered grades in ascending order you would get: A, A-, A+. Do you see why I am asking this? An easier alternative to writing a description is to email a copy of the workbook to me. Mike F"Kathy Houtami" wrote in message ps.com... Hi Mike What kind of detail do you need on my work book. I have an intermediate - advance level on VBA programming, though only in MS Access, but I assume not too much difference. This is my first attempt on try to program in excel VBA. Cheers Kathy On Sep 7, 11:00 pm, "Mike Fogleman" wrote: Yes, it can be done. I have built a report that part of it does something similar with a filtered list. Because it is all automated, the second list is filtered by each item from the first list so fast there is no time to read the results. So what I did was to transfer the results to a third sheet that could be named "Failed History" in your case. That sheet would list each failed student from the first list, and their grade history from the second list, with a blank row between each student's history, as a separator. We would need details of your workbook(s) in order to help you get started. What is your experience level in writing VBA code? None, Some, Intermediate, Advanced, Developer? Mike F "Kathy Houtami" wrote in message roups.com... 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- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP: Use the autofilter result on one workbook to filter the next list on another workbook | Excel Worksheet Functions | |||
Sum data in workbook 1 and write result in workbook 2 | Excel Programming | |||
Code to Advance filter a list in a shared workbook | Excel Programming | |||
Get the list from Autofilter after filter some criteria | Excel Programming | |||
Product Price List with 14k records, filter w/out using AutoFilter | Excel Worksheet Functions |