'transfer' filter? (2kSR1)

[itshimagain]

This might be 'easy', but my brain is in the wrong gear....
Sheet 1 is a list, about 600 records of 24 fields.
Sheet 2 contains columns 1 & 2 of sheet 1, (put there by formulas in sheet 2 of the type = sheet1!a1, sheet1!b1, etc.) plus about 50 columns of stuff not on sheet 1.
What I want is this: if I filter in sheet 1 on column x, the only records displayed on sheet 2 are to be the ones satisfied by the filter criteria on sheet 1.
I suspect the answer(s) involve one or more of =Indirect, =Offset or =VLookup, but brain still in wrong gear.
A gentle shove in the right direction, please?
Thanks

[HansV]

The filter you apply on Sheet1 is not necessarily on columns that are repeated on Sheet2?

[itshimagain]

That's correct, Hans. I could copy the column concerned onto sheet 2 and then hide it? If necessary, that is.

[HansV]

The workbook I have attached assumes that the fields on which you filter are in both sheets. It uses the Worksheet_Calculate event of Sheet1 to "copy" the filter from Sheet1 to Sheet2.

[LegareColeman]

Hans has given you a solution that requires that the filtered columns exist on both sheets. A VBA routine that does not have that requirement and hides the appropriate rows (not using filters on the second sheet) would be possible if the entries in columns 1 & 2 uniquely identify rows (unlike the rows in Hans' workbook). If that is not the case, how would the code know which row in sheet 2 to hide.

[itshimagain]

Thank you very much indeed Hans, and Legare for comment.
Harder than I thought!
The background is that I'm having to do in Excel what should really be done in Access, i.e splitting a big and ugly list into 'tables' each containing the fields I want, filter-able and sort-able.

[HansV]

<img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> I had almost suggested that you use Access for this instead of Excel! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

[itshimagain]

Hans, your worked example works fine (of course!) but I can't find the named macro and associated code which does it.
Is it hidden somewhere?
Sorry to be a pain!

[HansV]

It is a worksheet event procedure in the Blad1 (Sheet1) worksheet module.
The standard module Module1 only contains some global code.
The workbook module ThisWorkbook also contains a piece of necessary code: the Workbook_Open event procedure initializes the rngA variable.

[LegareColeman]

Attached is a version of Hans' workbook that does not require filters on Sheet2, and does not require that the column filtered on Sheet1 be included on Sheet2.

[HansV]

That's a very nice solution <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15> but the code will now run for every recalculation of Sheet1.

[LegareColeman]

Yes, but it does work for all of the Sheet1 filters.