4

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.

Improve this question

4 Answers 4

Reset to default
1

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.

Improve this answer
1

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

Improve this answer
5
  • 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, 2012 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.
    – Aprillion
    Oct 15, 2012 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
    – Aprillion
    Oct 15, 2012 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, 2012 at 18:35
  • @deathApril You are overthinking this way too much. apply the KISS principle as much as possible.
    – GoldBishop
    Oct 15, 2012 at 20:35
1

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

Improve this answer
1
  • @tarheel edited my original posting, to add in another option so if you need to do more than 2 Criteria filtering.
    – GoldBishop
    Oct 15, 2012 at 20:36
0

An easy (if imperfect) solution - After the word 'Header', simply type a space followed by 'Metric 1' then change the color of the Metric 1 text to match the background...in this case white. It becomes invisible but it will still show up in the filter, if selected. You just have to remember to tick both the 'Header' and 'Header Metric 1' boxes in the filter...hence the imperfection.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .