Horizontal autofilter

QuestionEdit

QUESTION: Hey I was wondering if you knew anyway to do a "horizontal" autofilter. Basically, I have the label "Segment" in cell C9. Cells C9-BA9 have different equipment segments (Hence the "Segment" lable). I have the same thing happening in D10 with the label "Phase". Cells D10-BA10 then have Phases 1, 2, 3, 4, and 5. I would like horizontal autofilters that allow me to filter the "segments" in row C and the "phase" in row D. Do you have any ideas on this? As always, let me know of any questions.

Taylor

ANSWER: Taylor,

there is no built in support for it.

Some possible workarounds

put in a couple of blank rows at the top

in C7 you could put in the name of a segment then in D7 put in the formula =if(D$9=$C7,"Yes","No") then drag this formula to the right to BA7

in C8 you could put in a Phase then in D8 put in the formula =if(D$10=$C8,"Yes","No") then drag this formula to the right to BA8

in D6 put in the formula

=if(OR(D7="No",D8="No"),1,"")

and drag fill out to BA6

now Select row 6 and do F5 to get the goto dialog, choose special and then select formulas and uncheck everything but numbers. This will select all the columns that need to be hidden. Do Format=>Columns=>Hide. The location for these actions would be different in Excel 2007 - the format for hiding and unhiding rows and columns is on the Home tab in the Cells Button, choose the format dropdown. May sound a bit cludgy, but it should work. You could expand it to filter on only one column. You could even use data validation with the list option to put dropdowns in column C.

Another approach would be a macro.

---------- FOLLOW-UP ----------

QUESTION: Your solution worked for me, but I'm not finding a way to easily repeat this. In other words, what do I do when I want to change the segment or phase and see the results then? I may have missed something but I've ran through your steps several times and it always works right away, but when I can my phase or segment input, the data becomes irrelevant.

Also, if a macro is an easier solution, do you have any ideas? I'm definitely pro-macro!

AnswerEdit

Taylor,

You would need to unhide the columns and then just apply new criteria - I believe that is what you are asking:

you would need to select row1 and do format=>Columns=>Unhide (or in Excel 2007, choose the format button on the home tab, then select unhide and select columns).