Autofilter via Formula - Help needed

[punsini]

Hi, I was wondering whether you can help me solving a complex Excel Problem (I have XL2003). That is, I want to extract data from a table in a sheet via cell formula and display it in a new sheet. My problem is that I have dynamic criteria. That is, I am looking for a formula (no VBA) that will work like Autofilter in a sheet.

Example: Table one contains "Car", "Model", "Year", "Country", ""Region, "New/Used". The 6 criteria are to be selected by drop down list in cells A1:A6 and when I select in A1 for car criteria "BMW", then I want to see all BMWs extracted and displayed in the new sheet. When I subsequently then enter in A2 "530" for the Model then the extracted data should contain all BMWs that are Model 530 etc. If I clear value in A2 to blank cell it should show again as per existing value in A1 all BMWs. If I clear now value in A1 all data should be extracted from base sheet containing all data. Hope this is somewhat understandable. Thanks for your help!

 

[storm8]

I dont think this is possible (at least not user friendly way) only with worksheet functions. however you could use filters.

try following:

copy all your table to new sheet so you have at lest 3 rows above the table empty (ie headers start at row 4)

copy headers in of the table into very first row
write criteria just below the new headers

now on card data under sort & filter select advanced

here you define the data range (your table) and criteria range (first two rows of data - with headers and criteria)

this should do the trick. you can also select to copy the data elewhere, however its quite anoying to have to set up the filter every time.

 

[storm8]

this also works with several criteria, if you want to filter more makes, you place "bmw" right below "manufacturer" in filter section and you place "audi" right below "bmw". dont forget to extend the filter range