Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Consider a spreadsheet that starts looking like this:

Sample Original Spreadsheet

I want to be able to filter for Name 1 on the left column and have it look like this:

Sample Filtered Spreadsheet

Yes, I realize that the simple answer is to filter for Name 1 and Header, but I have other people using this spreadsheet that don't seem to get that. So, how can I make it foolproof for them and make it impossible to filter out the rows that have Header in the left column?

Please Note: There are factors outside of the simplified example shown as to why they are going down instead of going across.

share|improve this question
add comment

3 Answers

Best approach would be to make it a namedrange, or Table for expandable range, and reference the Range instead of a specified declared range.

With that being said:

Range("Table2").AutoFilter 1, "header", xlOr, "Name1", False

This works if you have only two basic criteria. If you need more than two criteria may want to look at restructuring your NamedRange/Table so you can filter off different field/columns.

Edited 2012-10-15 @ 15:29

Did some further research for my own personal need and found that you can also do this if you have a Multiple (more than 2) Criteria to include:

Range("Table2").AutoFilter Field:=1, Criteria1:=Array("val1", "val2"), Operator:=xlFilterValues

Referenced from: VBA Tips & Tricks - Excel VBA AutoFilter: Multiple Criteria

share|improve this answer
    
@tarheel edited my original posting, to add in another option so if you need to do more than 2 Criteria filtering. –  GoldBishop Oct 15 '12 at 20:36
add comment

i suggest changing your data presentation design

or at least to use a 3rd column with a formula like this so users will see the name of the metric even if they filter just a name =IF(A2="Header";B2;C1)

enter image description here

share|improve this answer
    
Correct me if I am wrong, but your answer would only help me filter on column 3 to each metric one at a time. I want to see all metrics for Name 1, with all headers for each metric included. Filtering out Name 2 through 5. –  tarheel Oct 15 '12 at 12:29
    
@tarheel uh,, have you had your morning coffee? if you filter by name and nothing else, you will see all the metrics - but the names of the metrics will be displayed in column 3 instead of as headers, if that is a showstopper for you, please explain why. –  deathApril Oct 15 '12 at 15:49
    
* if you need the headers because the end users don't need to see the metrics, but to copy them somewhere and these end users don't know how to filter 2 items at a time, you are in real trouble –  deathApril Oct 15 '12 at 15:52
    
I don't drink coffee. The same people that can't understand that filtering for 'header' and 'name 1' at the same time, are not going to understand when what would be the headers jump off to the right side. Plus, in reality the there are multiple columns in each Metric such as Actual | Budget/Planned | Missed/Made by % –  tarheel Oct 15 '12 at 18:35
    
@deathApril You are overthinking this way too much. apply the KISS principle as much as possible. –  GoldBishop Oct 15 '12 at 20:35
add comment

Well, first, this is not how excel would expect your data, that is why you will most probably have to use some VBA-macro for this to be solved in the way you described.

Excel would expect such data to be arranged like this:

Header; Metric 1; Metric 2; Metric 3

And it would even be easier to create a result like you have asked, when the raw-data would be in this order.

However, to solve this as a macro, you would have to obtain a list of your names, create some dialog to select a name, crearte the basic macro which would take the name add the header and create the filter automatically and create a button or shortkey to assign it with the macro.

Another way could be to use an advanced filter: http://office.microsoft.com/en-us/excel-help/filter-by-using-advanced-criteria-HP005200178.aspx

There are just some hickups with the updating behaviour, but I managed to run this with this setup:

Table1:

A      | B        | C
-----------------------------------------------
Header | Metric 1 | Name1 (this is your filter)
Name 1 | 100      |
Name 2 | 300      |
Name 3 | 200      |

Table2:

A
----------------
Header
="=" & Table1!C1
'=Header

Then you setup an adavaced filter for Table1!A1:B4 and with criteria of Table2!A1:A3.

Those are your options I would say.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.