Filtering on multiple Worksheet

[French_Frize]

Hi everybody,

I have a small technical issue with a Database that I am writing.
In this database I have several sheets, referencing test reports. A test reports can have several tests in it.
The idea behind my database is that the user only fills in the Data Sheet and all the other sheets populate themselves with formulas using values from the Data sheet.
Line X corresponds to the same test on all my different sheets.
I can filter my Data sheet using all kind of criteria, but I would like that when I do filter Data sheet my other sheets only display the corresponding filtered lines so the display is consistent on all my sheets.

I didnt't find a way to do that so far and I think the solution would be to use VBA, but I don't know this tool.
Does anybody have an idea to help me?

Thank you

Adrien

[French_Frize]

Anybody could help with my problem?
I couldn't find a solution anywhere...

[oeldere]

You get better help on your question if you ost an excel file, without confidential information.

Please also add the desired (expected) result in your sheet.

[French_Frize]

Thank you for the advise oeldere.
Here is a simplified version of the Database. Database.xlsx

On the Screen Shot you can see the unfiltered sheets.

Unfiltered Data.PNG
Unfiltered Test 1.PNG

Then I filter my Data sheet Filtered Data.PNG and I would like my other sheet to look like Result expected on other Sheets.PNG

Thanks for your help

[oeldere]

why not all data on 1 sheet?

see the attached file.

[French_Frize]

The actual database is very large and has a lot of columns, so it is easier to read this way.
Plus it is clearer if have one tab per type of test (7 type of tests).

[newdoverman]

What do you call "very large"? Organizing the data the way that you have is quite problematic to work with. oeldere,s suggestion is a much better way of doing things.

[French_Frize]

Very large means 1-200 columns.
The idea behind having several worksheets the way I did is the user when filling in the database, only has to enter the data in the "Data" sheet and all the other "Tests" sheets populate automatically (using formulas and so on). This choice was made to made it more "user friendly" since several people will have to use this Database and more readable.

Like I said one given row corresponds to the same test in all my sheets. There must be a way to filter on Data Sheet and only show the "visible" row numbers of Data onto the other sheets.

[newdoverman]

You will need at least one common field on all your worksheets.

With what you describe, I recommend a relational database instead of Excel.

[French_Frize]

The common field between all my worksheet is the row number (does it count?)
I know Excel is not the best tool to make a user friendly Database but it is the first step of the project, in the future we will probably move to another tool but for right now we want to stick to Excel.
I'm pretty sure there must be a way to do what I'm trying using VBA, unfortunately I don't know this tool.

[newdoverman]

No, the row number is just a row number and not a field in any of the records. For the row to be significant, all records on all worksheets would have to be exactly the same for that row and absolutely no sorting and inserting of records.

[oeldere]

Maybe like this, see the attached file.

[sktneer]

Please find the attached sheet to see if this is what you are trying to achieve? Apply filter on the DATA sheet to see if you get the desired output on the TEST sheets.

[French_Frize]

Thank you both for your answers.
Sktneer is very close to what I'm trying to do! Could you please help me understand how you managed to do it?

Thank you very much to both of you for helping me

[sktneer]

Place the formula of col. G of attached sheet in post#13 in any unused column of your choice on the Data Sheet and copy down for rest of the existing rows with data. After this on each Test Sheet, place the formula (see the formula on Test 1 and Test 2 sheets of the attached workbook) in the columns A and B and copy down. While doing so make sure no filter is applied on any column on the Data Sheet, this will hep you to know that how long you have to drag the formula down in a column to have data from the Data Sheet. Now right click on Test 1 Sheet Tab --> View Code. Here you will find a code for Worksheet_Activate Event, copy the code and place this code on each Test sheet by right clicking the sheet tab --> View Code --> and pasting the copied code on the opened code window. This code hides the blank rows generated after applying the filter on the Data Sheet, so you view only the rows with the data. After doing this save your workbook as Excel Macro-Enabled Workbook and you are done with the setup.

Hope this helps.