Filters mess up comboboxes

I have 2 problems regarding the autofilter function and comboboxes.

I have an excel database with comboboxes. I want to be able to filter the data, therefore I am using the autofilter function.

1) When I filter, the comboboxes above a data section that is to be viewed end up at the first row, and comboboxes below end up in the first row following the viewed data set. Is there any way in which I can hide comboboxes when their rows are hidden? Today I use the form comboboxes, but I can use the control toolbox ones if necessary. Also, I can use some other function than the autofilter function if necessary.

2) Is there any way to make the filter function take information in comboboxes into account as well? For example, if I filter by weight and one option is available in three different weightes (displayed in a combobox) I still want that to be seen following filtering (can I perhaps write information in the cell under the comboboxes? If so, can the filter function take several data in the same cell into account?).

I have Excel 2002.

Thank you very much!

On problem 1 I am now thinking about making a first row which is always viewed, where it doesn't matter if all comboboxes end up following the filter. Is it possible to lock one row, e.g. so it is not affected by the autofilter function?

Next step would be to hide it, but then the combo boxes seem to end up in the "last viewable row", is there some way to fix this?

Or maybe another solution would be preferable...

I still haven't solved the problem.

I am happy to give further information and explanation of the problem if needed in order to help me.

Thank you Rob. I will upload two samples in order to explain my problem better.

The first file (database) illustrates what I would like it to look like. But now, if you filter you will see the combo boxes end up in funny places.

I will only have to be able to filter according to the white columns (even though it would be even better to be able to filter according to all categories). Ideally I would like the filter to recognize the data in the combo boxes, but I can avoid this by making differences row for for each alternative. However it is not possible to do this for all yellow columns as well (and thus avoid the combo box problem alltogether)as the original database include hundreds of combo box alternatives, which make tens of thousands different combinations possible. Here I have used the forms combo boxes.


In my second file (database(draft)) there is another kind of combo box that actually fixes the problem (as it works fine with the filter function). However, I don't know what kind of combo box this is, as I got the document it was already applied, and I can't alter the combo box in any way, not in design mode in control toolbox either (and no the document is not protected). Do any of you know what kind of combo box this may be?

Note that I've now upgraded to Excel 2003.

I finally found out it is a validation data list (in database (draft)), and not a combo box. Now I should be able to change all combo boxes to validation data lists, and then the messed up combo box problem is fixed.

As data validation lists seems better fit for the autofilter function, do anyone know if it's actually possible to include all options in a data validation list in the filter critera (before choosing an option)?

Rob: Thanks for the input. I will start to think about the headlines in the raw data as well, as in the original document it is actually a mess right now.

"do anyone know if it's actually possible to include all options in a data validation list in the filter critera (before choosing an option)? "

With this I mean that if I have a data validation list in a cell, for example consisting of the data "A", "B" and "C". Then when I filter the column according to "A" I would like to view the row with a data validation list where "A" is one of the alternatives even though "A" is not chosen (i.e. I would like to hide all rows where the A option is not available).

Am I more clear now?

Yes I do have different data validation settings for cells in the same column. I will explain why it has to be this way:

Let's say the database is about different car possibilities. In the column "colour", different options are available for different cars, for example a Mercedes may be available in black, white and red, and a Ford is available in white and blue. I want the data validation list to display only possible colour options for a certain car.

Ideally I would like to be able to filter according to colour, for example if I choose that all cars available in red should be displayed, I would like to display all cars where red is available among the options in the data validation list. But I understand this might actually be impossible to do when using data validation lists.

Now I have solved the problem by making unique rows for all options in the most important columns, such as for the axle configuration (i.e. Ford 4x2 and Ford 4x4 has different rows). Thus data validation lists are avoided in these columns, and filtering is not a problem.

For now I am happy only to be able to filter a these few columns. But thank you for all your help!

I will still watch this thread, so if you feel like coming up with any solution enabling filtering of columns with data validation lists as well, please post your solution here.