Automatically keeping two excel data tables in-sync (w/out VBA)
- by Neil
I'm putting together a workbook for tracking a stock portfolio. The primary sheet contains a table with the list of the transactions. From this I would like to create an overview table on another sheet with only one row per unique stock symbol that includes things like cost basis, returns, etc. The problem is that nothing I've tried updates the overview table correctly when rows are added to the transaction table. The closest I've got is something like the following:
http://www.get-digital-help.com/2009/04/14/create-a-unique-alphabetically-sorted-list-extracted-from-a-column/
However, this requires applying that formula to every cell in the primary column of the overview sheet. And even then the range of the table isn't extended down to include new rows as they become valid. Essentially I'm looking for a way that auto-adds rows to a table and copies the formula based on a different table changing without using VBA.
Trivial example data
Sheet1
Symbol Type Shares Price
F Buy 100 12
MSFT Buy 100 25
MSFT Sell 50 28
F Buy 100 16
Sheet2
Symbol Quantity
F 200
MSFT 50
