Excel – Automatically keeping two excel data tables in-sync (w/out VBA)

microsoft excel

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 previous rows formula based on a different table changing without using VBA.

A concrete example may better explain my goal. Assume that GOOG was just added and the other transactions already existed.

This is the closet I've got to getting the auto updating behavior.

Sheet1
Symbol     Type    Shares    Price
F          Buy     100       12
MSFT       Buy     100       25  
MSFT       Buy     50        28
F          Buy     100       16
GOOG       Buy     25        550

Sheet2
Symbol                              Quantity
F                                   200     
MSFT                                150
------------------------------------------------------------ // Table Boundary
GOOG                                25
{=UNIQUE_LIST(Sheet1[Symbol], A5)}  =SUMIF(Sheet1[Symbol], A5, Sheet1[Shares])
{=UNIQUE_LIST(Sheet1[Symbol], A6)}  =SUMIF(Sheet1[Symbol], A6, Sheet1[Shares])
... // Formulas start at row 2 and extend down tons of extra rows
    // Table boundary didn't extend to include GOOG

Ideally I want Sheet2 to look more like this where the formulas are copied down only when needed

Sheet2
Symbol                              Quantity
F                                   200     
MSFT                                150
GOOG                                25
------------------------------------------------------------ // Table Boundary
// Table extended and no extra formulas

Adding a row

Best Answer

Remeber that there are usually many ways to achieve the same result with excel. Maybe you can use database functions. In the example you've given if you reference the database as Sheet1!A:D or define a name that refers to that reference then you can add as many lines as you like. Unfortunately database functions are ugly and difficult to use due to the way you must specify criteria.

Another option would be to use an array forumla. something like: {=sum(if(sheet1!a2:a999="MSFT",sheet1!b2:b999,0))}

Related Question