Reference and filter data to other sheet in Excel
Is it possible to reference data from one Excel spreadsheet to another spreadsheet and filter it? The data on the new sheet should be updated automatically. E.g.
Sheet 1
colA|colB|colC xxxx|1111|2222 yyyy|3333|4444 xxxx|5555|6666filter by xxxx:
Sheet 2
colA|colB|colC xxxx|1111|2222 xxxx开发者_如何学Go|5555|6666Sheet 2 should be automatically updated if data on Sheet 1 changes
This works in Excel 2010:
- Insert > Pivot Table
- Add colA, colB, and colC as Row Labels
- PivotTable Design ribbon bar (Excel 2010)
- Subtotals > Do not show
- Grand Totals > Off for rows and columns
- Report Layout > Show in tabular form
- Report Layout > Repeat all item labels
- PivotTable Options > deselect the +/- buttons
- You should now essentially have a copy of your table
- Filter by whichever field you wish
This gets you close to what you want. You will still need to refresh the PivotTable (Ctrl + Alt + F5 to refresh all in the workbook).
This is why pivot tables exist. It will do this automatically for you.
精彩评论