Viewing 7 posts - 1 through 7 (of 7 total)
  • Excel help – Clever filtering needed
  • WorldClassAccident
    Free Member

    I have 3 sheets in an Excel spreadsheet.
    Sheet 1 is the summary sheet which I will describe later.
    Sheet 2 contains hundreds of rows of data with an ID field in column A
    Sheet 3 contains hundreds of rows of data with an ID field in column A

    The summary sheet should have as cell where the user types in an ID value. It then has two further cells showing the count of rows in sheet 2 and sheet 3 with that value in their ID column (A).

    So far I can do it, now comes the problem. Once the user has typed the ID value on sheet 1, if they click on sheet 2 or 3 the records displayed should be filtered to only show those with the selected ID.

    At the moment they have to filter each sheet manually which apparently is not acceptable.

    Can you filter a set of rows on one sheet based on a value in a cell on another sheet?

    clubber
    Free Member

    Can you not use a pivot table (with its built in filter) to achieve that?

    TheFlyingOx
    Full Member

    Basic (and probably very messy) way of doing it would be to have sheets 2 and 3 hidden, and have sheets 2 and 3 do some kind of =MATCH() or =IF()* thing on sheets 4 & 5 based on the critera in sheet 1

    *or whatever the excel look up feature is called

    Fresh Goods Friday 696: The Middling Edition

    Fresh Goods Friday 696: The Middlin...
    Latest Singletrack Videos
    RobHilton
    Free Member

    Pivot sounds sensible solution.

    If you *need* to do it the way you describe then you need a little macro.

    If noone else volunteers it up in the next couple of days I’ll do it – busy for the next 2 evenings

    WorldClassAccident
    Free Member

    Can’t use pivots as the data on sheet 2 and 3 are dumped there from another tool.

    Hiding them and having some extra sheets with matching is looking like it’s worth a try

    db
    Full Member

    I think you would have to refresh the filter using changing the sheet as the trigger and a little bit of VBA if I understand what you are trying to do.

    WorldClassAccident
    Free Member

    The user has changed their mind so the problem has gone away.

    Thanks anyway!

Viewing 7 posts - 1 through 7 (of 7 total)

The topic ‘Excel help – Clever filtering needed’ is closed to new replies.