dynamic filtering of data with multiple criterias

[Agreth]

Hi all

Trying to set up the following spreadsheet. I'v got a database on Sheet A ("Datenbank" in attached file) with multiple columns (date, customer, description, etc.) where I'm constantly adding new rows with data. On sheet B ("Suchmaske" in attached file) I'd like to have an additional database which should show only the rows (without blank rows between) that are based on multiple criteria, such as Date between (>= <=), customer, description etc. that I can change anytime. The database on sheet B should dynamically update/ adding new rows that I have added on sheet A. Is there a way to do that with array formulas or only with VBA?

For better explanation I have attached a sample worksheet. The green highlighted cells on sheet B ("Suchmaske" in attached file) are the cells where I'd like to enter the criteria values.

If anyone could give me a hand I would really appreciate that.

Thanks in Advance
Agreth

[CAntosh]

Welcome to the forum!

If I'm understanding you correctly, then I would advise turning your datasheet on "Datenbank" into a table so it automatically incorporates new rows. You can then use the following formula in B7 of Suchmaske. It should be array entered (confirm with Ctrl + Shift + Enter instead of Enter):

=IFERROR(INDEX(Table1,SMALL(IF((IF($C$6="",1,$C$6=Table1[Kunde]))*(IF($D$6="",1,$D$6=Table1[Beschreibung]))*(IF($E$6="",1,$E$6=Table1[Projekt '#]))*(IF($F$6="",1,$F$6=Table1[Rechnungs '# Kunde]))*(IF($B$2="",1,$B$2<=Table1[Datum]))*(IF($B$3="",1,$B$3>=Table1[Datum]))*(IF($B$6="",1,$B$6=Table1[Posten])),ROW(Table1[Posten])),ROW(1:1))-1,MATCH(B$5,Table1[#Headers],0)),"")

Fill right, then fill down beyond what you will need. It should automatically update as you change the search/filter terms or update "Datenbank". The formula will still work without a table, you'll just have to change all of the table references to the actual columns. Take a look at the attachment and see if it's close to what you're looking for:

[Tsjallie]

That would require some vba using the change event of sheet A for synchronizing sheet A and B.

But may be you could establish what you're after by just putting an autofilter on the database (Sheet A).
If you change the format of the cells holding dates to the date format that will offer you quit some selection option.
But may be there are other reasons you want to do your selection on a copy database which I'm not aware of.

And yes turning the database into a table is highly recommended.