Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'transfer' filter? (2kSR1)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'transfer' filter? (2kSR1)

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

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'transfer' filter? (2kSR1)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'transfer' filter? (2kSR1)

    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.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'transfer' filter? (2kSR1)

    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.
    Legare Coleman

  6. #6
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'transfer' filter? (2kSR1)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'transfer' filter? (2kSR1)

    <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>

  8. #8
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'transfer' filter? (2kSR1)

    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!

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'transfer' filter? (2kSR1)

    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.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'transfer' filter? (2kSR1)

    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.
    Legare Coleman

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'transfer' filter? (2kSR1)

    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.

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'transfer' filter? (2kSR1)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •