Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question

5 Answers 5

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))}

share|improve this answer
    
As I mentioned in the OP, I don't want to reference an arbitrarily large range of data. I want the table in Sheet2 to grow automatically when a row is added to Sheet1. This is necessary so that I don't have to copy a relatively complex formula all the way down the first column of sheet 2. Also, I would like to be able to use a structured reference to the first column of Sheet2 for parameters to a web query, e.g. Sheet2Table[Symbol]. I'll update my example to better describe my scenario –  Neil Jan 30 '11 at 1:50

You write:

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.

You cannot escape from these two requirements if at least you don't set the maximum length of the list in your first sheet.

share|improve this answer

Neil,

If you are using XL 2003 or later, you should try putting your source data in a List (XL 2003) or Table (same thing, renamed in XL 2007, 2010). Then use your List/Table as the source of a Pivot Table on a different sheet. This meets your requirements for both data and report as I understand them. The pivot source will expand and contract with the List/Table.

The only caveat is that Pivot Tables don't refresh automatically when their source data changes. You'll have to hit the Refresh button. If you are able to use VBA then you could write event code that triggers a pivot table refresh each time a cell within the List/Table changes.

share|improve this answer

I work with pivot tables a lot. When you set your array, manually type in the ending cell.

For examble I have data that starts off with the range A11:C2500.

If I anticipate that the data will grow to row 10000 I will manually change the array to read A11:C15000 to ensure I have room for growth.

If you do this you will see your Pivot table agrow as new items are added to the transaction list.

share|improve this answer
up vote -2 down vote accepted

It seems that someone was inspired by my question and wrote up an entire blog post about how to do this

http://www.get-digital-help.com/2011/01/31/tracking-a-stock-portfolio-in-excel-auto-update/

share|improve this answer
3  
A link as an answer is not helpful to anyone, especially if that website goes offline. You should write up the actual answer here on SuperUser. –  ÃŁŁǫǛȉЖΦΤїҪ Jan 17 '13 at 22:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.