Solved

Add AutoFilter In Excel Elsewhere

Posted on 2014-04-23
6
327 Views
Last Modified: 2014-06-23
I have data in an Excel 2007 workbook.   I used the auto filter function on the first row of information (row 3).  Like this:
             Column A    Column B
row 1- empty
row 2- empty
row 3- name          color
row 4- john              red
row 5- Rick               green

I need to have one of the columns with the filter displayed elsewhere.  So the workbook would look like:

             Column A     Column B       Column C
row 1-                                                 drop down filter for names
row 2- empty
row 3-  name           color
row 4- john              red
row 5- Rick              green

Is this possible?  If so, how can I do this?
0
Comment
Question by:jjrr007
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 19

Expert Comment

by:regmigrant
ID: 40018429
When you use autofilter it takes the row you have chosen as column headings and gives a drop down for all the items in the column below - so the filter columns all have to be in a single line.

If you explain what you are trying to achieve there maybe a different way to do it but autofilter won't let you work on different rows at the same time.
0
 
LVL 1

Author Comment

by:jjrr007
ID: 40018531
Thx. It doesn't have to be the auto filter function.  I just need a drop down box that selects the values I select from it.

In the example I provided, I would select the items from a drop down box and only those appear.  However, the issue is that I need it away from the column headings.  Is there any way to do this?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40019438
Which version of Excel do you have? Excel 2010 onwards has a feature called "Slicer". You create the slicer for a list of entries and it shows like a large drop down box with the first few visible and others available by scrolling down. When you select an entry form the slicer it will filter the list for that entry.

Other option would be Advanced Filter rather than Auto Filter. You create a list of Filter entries and run the Adv Filter using the filter list as a criteria list. Downside of Adv Filter when using it off to the side of the data list, the criteria rows may get hidden by the Filter; although if your data is row 3 down and you put your filter option in row 2 it would be ok.

Another option, have a Data Validation dropdown based on your data list and a Spreadsheet Change Event macro to filter on the entry.

Thanks
Rob H
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:jjrr007
ID: 40027464
Thanks for your response and sorry for the delay.  I am using Excel 2007.  How do I create an advanced filter and a data validation drop down box?  I am not familiar with them.
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40027519
In xl07, go to the data tab and in the Sort and filter group alongside the large Filter button there is a smaller button with advanced against it. This will bringbup the AF wizard.

There are 3 requirements for AF
1 data source
2 criteria source
3 destination location

The third option will be greyed out unless the copy to another location option is ticked. If not ticked the data will be filtered in place.

1 Data source - fairly obvious yiur data set, including the headers.
2 Criteria source - a separate table with at least one of the headers from the dataset and at least ine criteria cell under it. If using multiple columns, criteria values on the same row are counted as AND criteria. Multiple values in the same column are treated as OR criteria, hope that makes sense.
3 Copy location - if copying all columns this can just be a single cell. You don't have to copy all columns and they don't have to be in same order but do have to be the same as the column headers in the data.

Quirks
When running AF manually, the cursor has to be on the sheet to which you want to copy the data and if this sheet already contains the headers, the cursor needs to be away from tthe headers so that the wizard doesn't try to use the headers as data and get confused with onlybone row.
When copying to another sheet, if a previous filter has been run, the new filter will overwrite the previous filter unless it has a smaller result set whereby it will only overwrite rows covered by the new set.

Hope this covers Adv Filter sufficiently.

Thanks
Rob H
0
 
LVL 1

Author Closing Comment

by:jjrr007
ID: 40152625
Sorry for the delay.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question