Subscribe now and choose from over 30 free gifts worth up to £49 - Plus get £25 to spend in our shop
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?
Can you not use a pivot table (with its built in filter) to achieve that?
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
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
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
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.
The user has changed their mind so the problem has gone away.
Thanks anyway!