The AutoFilter option is a handy tool for filtering data the way you want. But it can filter data in place only (listers - correct me if not so). The Advanced Filter tool can help achieve the desired result. Steps below:
1. Specify the filtering condition in the cells that are expected to not contain data in future. Let's assume BA1 and BA2 are two such cells. BA1: Month BA2: Nov 2. Select the data to be filtered. A1:Z100, for instance. 3. Press ALT+A followed by Q to open the Advanced Filter dialog box. Alternatively, press ALT+D, F, A. 4. Specify the following in the dialog box: a. Filter the list = Copy to another location radio button. (press SHIFT+TAB once after opening the dialog box to reach out to the two radio buttons) b. List Range = $A$1:$Z$100 (this should be automatically filled if you have selected the correct data) c. Criteria Range: BA1:BA2 (the place where we specified the filtering condition in step 1 above) d. Copy To = AA1 (the target location starting cell) 5. Activate the OK button to apply the filter and have the filtered data copied to the desired location. Limitations of Advanced Filters: 1. It cannot Copy data to a different sheet. This limitation doesn't affect the given scenario as the target location is on the same sheet. 2. It copies all columns in the selected data, there's no option to copy selective columns. However you can hide columns that are not required at the target location. If you want the data sorted after copying then sort the data before applying the filter. hth. -- Regards, Zujar... An optimist laughs to forget, whereas a pessimist forgets to laugh! From: Mukesh [mailto:mukeshbaviskar....@gmail.com] Sent: Wednesday, December 19, 2012 9:37 AM To: Access India Cc: Zujar Shabbir Kanchwala Subject: if formula text I have a database in which I want to sort out the list of teachers for particular course in particular month. Query is as follows: column 'b' name of teacher Column 'e' name of school column 'g' address column 'p' selection month when I type a name of month the data of these three cells should directly go to the selection list of that particular month. if 'p5' = nov the data of these three cell should go in selection list in same sheet columns aa5-ac5 respectively. is it possible? Solution please. Thank you. Regards Mukesh Baviskar mobile: 9403161157 Register at the dedicated AccessIndia list for discussing accessibility of mobile phones / Tabs on: http://mail.accessindia.org.in/mailman/listinfo/mobile.accessindia_accessindia.org.in Search for old postings at: http://www.mail-archive.com/accessindia@accessindia.org.in/ To unsubscribe send a message to accessindia-requ...@accessindia.org.in with the subject unsubscribe. To change your subscription to digest mode or make any other changes, please visit the list home page at http://accessindia.org.in/mailman/listinfo/accessindia_accessindia.org.in
