Is there a straight forward way to have data stored in one worksheet automatically filtered and displayed on a different spreadsheet. I want to be able to update the data in one sheet and have the filtered view on the other worksheet update automatically.
Excel – Dynamically filter data from one excel worksheet to display on another
microsoft excel
Related Solutions
Does the Filter -> Reapply command not do what you want? You can probably write a small VBA routine to run this command whenever the underlying data is updated.
I suggest using a PivotTable with AgentName as the Report Filter:
If Agents have so many clients that the width would be a problem, drag Client from Column Labels to Row Labels and change Design > Report Layout to Show in Tabular Form.
Best Answer
Another method using a data table
Using same Sheet1 Data
On Sheet3 select cell A5 and press Ctrl + T to create data table and select my table has headers and then OK.
Select cell A5 and A6 and drag across the required number of columns
and edit text to your column headings
In cell A6 add the following formula
and drag A6 across required columns then down required rows
then use filters as required.