Excel 2007: Help with filtering option

CodeRedR51

CodeRedR51

Premium
55,442
United States
United States
Ok, so I am doing this project at work and need to know if this is possible. Basically what I am looking for, is to make a drop down box based off of the "Make" column, and when you select an option in that dropdown it filters to show you only the cars associated with that particular make.

Example: If I select BMW 1-Series F20 from the drop-down (circled in red), it hides everything but the rows highlighted in green from the photo below. I can't seem to find anything about how to do it on the net because if I include the word "filter" in the search it just talks about the filter function in Excel 2007 and that's not what I need.

Anyone?

excel_filter_zps54d30114.jpg
 
I don't believe that's what I need. I don't need to filter the stuff myself, as I'm working on the sheet and can find what I am looking for. What I need is to be able to program the function into the sheet so that when I send this sheet to other employees they can use the drop-down and limit it to a particular section.
 
Can't the employee hit the dropdown, de-select the (select all) checkbox, and then check the box(es) for only the row(s) they need displayed?
 
How would excel know how many rows past "BMW 1-Series F20" to show?

Seems in that there are different number of rows per type of car.

Maybe instead of sorting by Column A, you sort by Column B? Seems like some the 1-series F20 different models still have the (F20) in them.

Does that help?
 
Thanks guys I think we figured it out. Had another employee working on it. Basically for it to filter all those rows we have to have the make on each of those rows. The drop-down box isn't what I want, but without actually programming it with code or whatever it's the best we can do for now.
 
Infinital-NG
How would excel know how many rows past "BMW 1-Series F20" to show?

Seems in that there are different number of rows per type of car.

Maybe instead of sorting by Column A, you sort by Column B? Seems like some the 1-series F20 different models still have the (F20) in them.

Does that help?
Click to expand...

One solution would be to add a helper column and assigning a value to each row belonging to the same model. Then Excel can sort the list according to the helper column. Like this:

skarmavbild20130513kl14.png


To get an automatic formula for the helper column this might work (assuming that the helper column is A, the model is in column B and that the list starts on row 2)

=IF(B2="";A1;B2)

If the model column (B2) is empty, it will fill the cell with the value from the cell above (A1). If it's not empty it will take the value from the model column. To fix the blank rows that separates each model, make the following adjustments (in blue):

=IF(C2="";"";IF(B2="";A1;B2))

This will make the formula search for a value in row C first (which is only empty on empty rows). If there is a value in the cell it will proceed with the previous formula.

If you want numbers instead of the entire model name in the helper column, this formula will do the trick:

In cell A2, enter the value 1.
In cell A3, type the following formula:

=IF(C3="";"";IF(B3="";A2;A2+1))

If the model cell (B3) is empty it will copy the value from the cell above (A2). If it's not empty (which is true if there's a new model) it will take the value from the cell above and add 1.
 
Last edited:

Latest Posts

Back